Display prior week data on 53rd week (and 1st week)

DAX DAX Calculations
1

Please refer to the uploaded PBIX file (mock-up) for the problem statement. This my first time leveraging the extended date query. To display the prior week quantity (in minutes), I have tried leveraging Sam’s suggested DAX formula. But it is not behaving as expected.

As explained in the problem statement, there is no fact table data beyond week ending 3/21/21. The 5.428M shown at the bottom for “PW Minutes” (for 1/9/22) is actually a piece of the 1/3/21 amount of 14.345M. Added with the 8.916M for 1/10/21, it equals the 14.435M (of 1/3/21).

Thanks in advance.

Here is the PBIX file: Data Model with Mockup.pbix (3.0 MB)

image

2

Hi @mdalton2100,

With the Extended Date table you can leverage the WeekOffset, see how this works for you

PW Minutes v2 = 
VAR CurrentWeek = SELECTEDVALUE( Dates_enhanced[WeekOffset] )
VAR Result = 
    CALCULATE( [All Minutes (sum)],
        FILTER(  All( Dates_enhanced),
            Dates_enhanced[WeekOffset] =  CurrentWeek -1
        )
    )
RETURN

Result

I hope this is helpful

1 Like
3

Yes, that worked beautifully! Offset parameter is an amazing thing.