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

[jasemilly]

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

[johnt75]

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.

[jasemilly]

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