Display the measure result of the same week last year?

Question

I have a table in Power BI with a measure calculating a rate for each week of the year using DAX Divide, I need to display the prior years result for the same week LY on the same row as a comparison column. I have the previous Year/Week as a data field to refer to. Example of the table below:

I've tried using the Filter function but to no avail as the Earlier function appears to not be available in DAX. I could add another two columns to then divide and essentially create the result from scratch for each column by offsetting the filter in the Calculate(Sum()) functions but I'm trying to avoid excessive calculation.

Year/Week   Previous Year/Week  Measure Previous Year Measure
2019 W10    2018 W10            3.3     3.1
2019 W09    2018 W09            3.3     3.1
….  …   …   …
….  …   …   …
2018 W10    2017 W10            3.1     2.9
2018 W09    2017 W09            3.1     2.9
…   …   …   …

Have a look at SAMEPERIODLASTYEAR - docs.microsoft.com/en-us/dax/sameperiodlastyear-function-dax — mxix
It is not clear the data model you defined. Also it is not clear the granularity / Primary Key of the table you are showing. Anyway, it is best practice is to define a Calendar dimension which is linked using at least an active relationship to your fact table. — Seymour

Marco Vos Marco Vos · Accepted Answer · 2019-06-21T08:47:25

Actually the EARLIER-function is available in DAX. Try something like this as a calculated column:

Previous Year Measure =
CALCULATE (
    MAX ( Table1[Measure] ),
    ALL ( Table1 ),
    'Table1'[Year/Week] = EARLIER ( Table1[Previous Year/Week] )
)

or

Previous Year Measure =
CALCULATE (
    MAX ( Table1[Measure] ),
    FILTER ( Table1, 'Table1'[Year/Week] = EARLIER ( Table1[Previous Year/Week] ) )
)

Display the measure result of the same week last year?

1 Answers