Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
I would like to have one table or matrix with a Sales Region, then columns for the following information:
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.
Solved! Go to Solution.
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.
@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)))
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.
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.