Max Date per Month

[greend21]

I have data like the below example and want my pivot table to only show the values for the max date per month. I have a variable to toggle the view so I tried =If($(vValuationDate) = 0, MonthName(Aggr(Max(ValuationDate), ValuationDate.autoCalendar.YearMonth)), ValuationDate) but it shows the values for 2015-01-22 and 2015-02-12. The other odd thing is if I remove the IF statement and only have =MonthName(Aggr(Max(ValuationDate), ValuationDate.autoCalendar.YearMonth)) I get the sum total for each month instead of a specific week's total. I would think the results would at least be consistent. Does anyone know how to get the desired format?

 Current format:

ValuationDate	Number
2015-01-02	131
2015-01-08	148
2015-01-15	234
2015-01-22	233
2015-01-29	165
2015-02-05	181
2015-02-12	72
2015-02-19	194
2015-02-27	74

Needed format:

ValuationDate	Number
2015-01-29	165
2015-02-27	74

[greend21]

Actually, do you know how to flag the last date per month in the load script? I was going to try another method using flags and set analysis but AGGR doesn't work in the load script.

[sunny_talwar]

If(Date = Floor(MonthEnd(Date)), 1, 0) as MonthEndDateFlag

[greend21]

Thanks. I should have worded my question better because I meant out of the dates I have in the data, for example if I do not actually have the last day of the month.

In case anyone else has a similar issue, I was able to solve my problem in the load script. Here is what I did as a reference:

[Can_UnallocCashSummary]:
LOAD
[LOB],
[ValuationDate],
[CANCashAge],
[CANBusinessType],
[ClosedAmt],
[ClosedCt],
MonthName([ValuationDate]) AS [CANMonth]
FROM [lib://Ad_Hoc_ QVD_Folder/Can_UnallocCashSummary.qvd]
(qvd);

LEFT JOIN(Can_UnallocCashSummary)

Load Date(Max(ValuationDate)) as CANMaxWk,
CANMonth
Resident Can_UnallocCashSummary
Group By CANMonth
;

LEFT JOIN(Can_UnallocCashSummary)

Load IF(ValuationDate = CANMaxWk, ClosedCt) as CANMaxWkCt,
IF(ValuationDate = CANMaxWk, ClosedAmt) as CANMaxWkAmt,
IF(ValuationDate = CANMaxWk, ValuationDate) as ValuationDate,
CANMaxWk,
CANMonth,
LOB,
CANBusinessType,
CANCashAge
Resident Can_UnallocCashSummary
;

[nitrog3n]

Hi Sunny, 

The expression you have shared is working perfect with me but the only issue is when I select any month I get data of that particular month.

My requirement is to get data for 6 months i.e if I select any month I should get data of that month + 5 previous months.

Expression used:

num(Sum({<Date={">=$(=Date(Num(MonthStart(AddMonths(Max(Date),-6))),'YYYY/MM/DD'))<=$(=Date(Num(Max(Date),-1),'YYYY/MM/DD'))"},MonthYear=>}
Aggr(If(Date = Max(TOTAL <MonthYear> Date),
count({<Date={">=$(=Date(Num(MonthStart(AddMonths(Max(Date),-6))),'YYYY/MM/DD'))<=$(=Date(Num(Max(Date),-1),'YYYY/MM/DD'))"},MonthYear=,
[Difference Category]={'7Days'}>}Distinct [DEVICE NO])),Date, MonthYear))

/

Sum({<Date={">=$(=Date(Num(MonthStart(AddMonths(Max(Date),-6))),'YYYY/MM/DD'))<=$(=Date(Num(Max(Date),-1),'YYYY/MM/DD'))"},MonthYear=>}
Aggr(If(Date = Max(TOTAL <MonthYear> Date),
count({<Date={">=$(=Date(Num(MonthStart(AddMonths(Max(Date),-6))),'YYYY/MM/DD'))<=$(=Date(Num(Max(Date),-1),'YYYY/MM/DD'))"},
MonthYear=>}Distinct [DEVICE NO])),Date, MonthYear)),'0.0%')