Compare values to same month in specific year

[PoPQ]

I'm interested in creating a measure to compare daily values aggregated to the month with the same month in 2019 as a % difference. For instance, I'd like to compare the sum of December 2021 'thruput' with the same value for December 2019, July 2020 vs. July 2019, etc. I'm not sure I can do this with a simple samperiodlastyear calculation because I always want the comparison year to be fixed at 2019. Data can be found here.

[mh2587]

Measure 1 2020= calculate(sum[value],DATEADD(DateTime[DateKey],-1,year)
Measure 2 2021= calculate(sum[value],DATEADD(DateTime[DateKey],-2,year)
Measure 3 2022= calculate(sum[value],DATEADD(DateTime[DateKey],-3,year)

[PoPQ]

I'm hoping to solve this as single measure so I can display it as a single trendline over the span of three years.

[v-kkf-msft · Accepted solution]

Hi @PoPQ ,

Please try the following formula:

Measure = 
CALCULATE (
    SUM ( 'TSA_thruput xlsx - Sheet1'[Thruput] ),
    FILTER (
        ALL ( 'TSA_thruput xlsx - Sheet1' ),
        'TSA_thruput xlsx - Sheet1'[Date].[Month]
            = MAX ( 'TSA_thruput xlsx - Sheet1'[Date].[Month] )
            && 'TSA_thruput xlsx - Sheet1'[Date].[Year] = 2019
    )
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PoPQ]

Thanks! Is there a reason this solution works with the given single-query dataset, but when I include a date table in the data model, the specified .[Month] and .[Year] hierarchies go unrecognized? It throws an error that says: "Column reference to 'Date' in table 'Thruput' cannot be used with a variation 'Month' because it does not have any."

[v-kkf-msft]

Hi @PoPQ ,

There are many reasons why the date hierarchy disappears. You can replace Sheet1'[Date].[Month] and Sheet1'[Date].[Year] with Month( Sheet1'[Date] ) and Year( Sheet1'[Date] ), or use your own created month and year columns, which does not affect the final result.

Best Regards,
Winniz

[mh2587]

= calculate(sum[value],DATEADD(DateTime[DateKey],-2,year)

[PoPQ]

thanks @mh2587 for the quick reply. that almost works but it is relative, where I want the comparison year to be 2019 regardless of whether I'm comparing a 2020, 2021 or 2022 month value to it.