topic Re: How to create top 5 previous year, allowing a this year / last year comparison in DAX Commands and Tips

How to create top 5 previous year, allowing a this year / last year comparison

jasemilly — Thu, 27 Feb 2025 16:11:10 GMT

Hi
I have created Clustered Column Chart that shows top 5 by category for the whole date range. Broken down by year/month. This works great. It handles if a category has a month were it would be outside the top 5. It's the top 5 for the date range and breaks down what these top 5 did each month.

From Aport To Htl Top 5 Countries = VAR TopFive = TOPN( 5, SUMMARIZE( ALLSELECTED(MergedToHtlFromAirport), Country[Country], "TotalSales", [Total # Rows From Airport] ), [TotalSales], DESC ) RETURN CALCULATE( [Total # Rows From Airport], KEEPFILTERS( TopFive ) )

I have taken this measure and altered to try and look at the previous year.

So I can compare year on year change.

Feb 2024 shows 1092 and happy with this LY showa 1146, this should be 1092.

This is the measure I created for the LY measure it doesn't quite work correctly and returns the top 5 qty for each month.

Rather than the top 5 for the date range and then showing the breakdown for each month.

From Aport To Htl Journeys Top 5 Countries LY = VAR TopFivePrevYear = TOPN( 5, SUMMARIZE( CalculateTable( ALLSELECTED(MergedToHtlFromAirport), SamePeriodLastYear('Date'[date] ) ), Country[Country], "TotalSales", [Total # Rows From Airport] ), [TotalSales], DESC ) RETURN CALCULATE( [Total # Rows From Airport], KEEPFILTERS( TopFivePrevYear ), SAMEPERIODLASTYEAR('Date'[Date]) )

Re: How to create top 5 previous year, allowing a this year / last year comparison

johnt75 — Thu, 27 Feb 2025 16:54:12 GMT

Try

From Aport To Htl Journeys Top 5 Countries LY = VAR TopFivePrevYear = TOPN ( 5, ADDCOLUMNS ( SUMMARIZE ( ALLSELECTED ( MergedToHtlFromAirport ), Country[Country] ), "TotalSales", CALCULATE ( [Total # Rows From Airport], SAMEPERIODLASTYEAR ( 'Date'[date] ) ) ), [TotalSales], DESC ) RETURN CALCULATE ( [Total # Rows From Airport], KEEPFILTERS ( TopFivePrevYear ), SAMEPERIODLASTYEAR ( 'Date'[Date] ) )

I added the ADDCOLUMNS around SUMMARIZE as you shouldn't use SUMMARIZE to generate calculated columns, always use ADDCOLUMNS or SELECTCOLUMNS for that.

The main problem I think is that you had the SAMEPERIODLASTYEAR in the wrong place. You want it when you are calculating the total, not when you are doing the ALLSELECTED.

Re: How to create top 5 previous year, allowing a this year / last year comparison

jasemilly — Thu, 27 Feb 2025 17:59:18 GMT

That doesn't work getting the same values as the query I posted.