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

[sunny_talwar]

May be try something like this

Sum(Aggr(If([Valuation Date] = Max(TOTAL <[ValuationDate.autoCalendar.YearMonth]> Valuation Date), Sum(Number)), [Valuation Date], ValuationDate.autoCalendar.YearMonth))

[greend21]

My original thought was using a calculated dimension to show the specific date thinking the measures would sum for those dates but that doesn't seem to be working. I tried what you provided as a measure but it did not work but I will try a few things based off a similar method.

[sunny_talwar]

What all do you have dimensions? Do you only have Valuation Date as a dimension or do you have others as well? Also, this is minor, but is your Date field ValuationDate or [Valuation Date] (with a space)... make sure you use the right field name throughout the below expression....

Sum(Aggr(If([Valuation Date] = Max(TOTAL <[ValuationDate.autoCalendar.YearMonth]> Valuation Date), Sum(Number)), [Valuation Date], ValuationDate.autoCalendar.YearMonth))

[greend21]

The row dimensions are LOB, BusinessType, and ValuationDate. The column dimension is AgeBucket.

[sunny_talwar]

Oh so you have a lot more going on then you posted here... would you be able to share  a sample to show what you have and may be share the expected output from the sample you share in an Excel file?

[greend21]

I'm not sure that I'll be able to post an example, but basically my information is like what I posted above. I can get the pivot to display only 2015-01-29 for the month of January but the expression is summing everything when I only want it to display the sum for that last week in January, or 165.

[sunny_talwar]

I want to help, but without seeing the whole picture, it would be difficult to offer much help.

I hope someone else is able to help you here.

Best,
Sunny

[greend21]

No problem. I was trying to use Aggr to get the sum of ClosedCt per the last week of the month but I can't get it right if it is possible.

[sunny_talwar]

okay 🙂