
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Max Date per Month
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 |
- Tags:
- pivot
- « Previous Replies
-
- 1
- 2
- Next Replies »

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
The row dimensions are LOB, BusinessType, and ValuationDate. The column dimension is AgeBucket.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I hope someone else is able to help you here.
Best,
Sunny

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

- « Previous Replies
-
- 1
- 2
- Next Replies »