Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 M...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

Anonymous , 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)))

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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.