I would like to have one table or matrix with a Sales Region, then columns for the following information:
- Max monthly rate for the region for 12/2020
- Max Monthly rate for the region for 12/2021
- Max YTD(yearly) rate for the region for 12/2020
- Max YTD(yearly) rate for the region for 12/2021
- Sum YTD (yearly) total for the region for 12/2020
- Sum YTD(Yearly) total for the region for 12/2021
Currently, I have had to have 4 different Matrixes(1 monthly, 1 peak 2020, 1 peak 2021, and 1 for aggregate numbers), side by side, repeating the Region each time, not a pretty site or very manageable.
My current YTD Max Measure reads as follows:
PeakRegionYTD = SUMX ( SUMMARIZE (TABLE, [Customer], [Region], [YearMonth].[Year], "PeakRegionYTD", MAX (TABLE[Sales])), [PeakRegionYTD]
If I then filter for the year, I get the result I want, but I cannot combine Max Month, MaxYTD, and Aggregate all together in one table. If there a measure that I could write that could utilize MAX and YTD for current year and prior year? I have played around with this and have been unsuccessful thus far. I am not able to post example data or I would, so please do not ask.