cancel
Showing results for
Did you mean:
Helper II

## Table or Matrix visualization - Max monthly numbers, YTD Max for regions and aggregate numbers

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.

1 ACCEPTED SOLUTION
Helper II

These calculations provided the max for the lowest level (which is customer, even if I choose Region), so when I have Region, it provided the customer with the month max for the year, not the max for the region.

2 REPLIES 2
Super User

@lsullivan6311 , You can try measures like

YTD Max= CALCULATE(Max(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Max= CALCULATE(Max(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

MTD MAX= CALCULATE(MAX(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD MAX= CALCULATE(MAX(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

Helper II

These calculations provided the max for the lowest level (which is customer, even if I choose Region), so when I have Region, it provided the customer with the month max for the year, not the max for the region.