Calculate versus prior year with multiple years of data

[alexpegg86]

Hi all,

I'm trying to calculate a "versus prior year" calculation in power bi. The complication is that i have 3 years worth of data.

MAT stands for Moving Annual Total (or last 52 weeks of data)... so MAT is the last 52 weeks, MAT-1 is the previous year of data, and MAT-2 the last year of data.

So i'm wondering if there's a way in DAX to write a versus prior year measure stating something like "if the date is the current MAT, then calculate MAT vs MAT-1, otherwise if the date is MAT-1, then calculate MAT-1 vs MAT-2".

The data i receive is every 4 weeks (so there are 13 data deliveries per year), as you can see in the Period End Date in the data. I've tried using Parallelperiod, and this nearly works, but it falls over when there are two data deliveries within the same period (as you can see in the "Value Same Period Last Year" column for 29/10/2023, as it's adding up the two data deliveries for October 2022).

What i'm trying to get to is calculate the value for the equivalent period last year, based off the Period and Year... so P6 2024 equivalent value would be P6 2023, and P6 2023 equivalent value would be P6 2022.

Any help would be welcome! See below a link to the pbix workbook and data file.

Thanks.

Data: https://drive.google.com/file/d/1dH6Vmr-2iCbWbnYi4-thft0pbYO3CznN/view?usp=drive_link

PBIX: https://drive.google.com/file/d/1STRUatexKkVLZO9ugAdCQuxPh8BnwN2c/view?usp=drive_link

[Anonymous · Accepted solution]

Hi @alexpegg86 

Please try the following measure:

Value Same Period Last Yearr = 
VAR _select_year = SELECTEDVALUE('Total Soft Drinks Output'[Year])
VAR _select_period = SELECTEDVALUE('Total Soft Drinks Output'[Period])
RETURN
CALCULATE(
    SUM('Total Soft Drinks Output'[Spend]),
    FILTER(ALL('Total Soft Drinks Output'),'Total Soft Drinks Output'[Period] = _select_period && 'Total Soft Drinks Output'[Year] = _select_year-1)
)

Value Same Period Last two Years = 
VAR _select_year = SELECTEDVALUE('Total Soft Drinks Output'[Year])
VAR _select_period = SELECTEDVALUE('Total Soft Drinks Output'[Period])
RETURN
CALCULATE(
    SUM('Total Soft Drinks Output'[Spend]),
    FILTER(ALL('Total Soft Drinks Output'),'Total Soft Drinks Output'[Period] = _select_period && 'Total Soft Drinks Output'[Year] = _select_year-2)
)

Result:

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[alexpegg86]

Thanks @Anonymous ! That has worked 😀