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"))

MTD MAX= CALCULATE(MAX(Sales[Sales Amount]),DATESMTD('Date'[Date]))

Learn Power BI - Full Course with Dec-2022, with new DAX functions like Window, Index, Offset !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Formatted Profit and Loss Statement with empty lines
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.

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### 2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors