Calculate Static Timeframes - Last Week, Last 4 Weeks, Last 52 Weeks over Prior Year - Customer Calendar, Power BI

[AmberM]

Hi All,

I am trying to create static timeframes for Last Week, Last 4 Weeks, Last 52 Weeks over prior year to provide a topline summary that can be printed and taken into meetings.

I was able to create Last4Weeks, but the formula I tried for Last4Weeks Prior Year does not work.

Last4Weeks formula:

Last4Weeks = CALCULATE([Total Sales],
    FILTER(ALL(Dates), 
Dates[Week Number] <= MAX(Dates[Week Number]) && 
Dates[Week Number]> MAX(Dates[Week Number]) -4))

PYLast4Weeks formula (does not work):

Last4Weeksly = CALCULATE([Total Sales],
    FILTER(ALL(Dates), 
        Dates[Week Number] <= MAX(Dates[Week Number]) -52 && 
        Dates[Week Number]> MAX(Dates[Week Number]) -49))

Here is an example of what I want the tab in my report to look like:

This is what my formula currently looks like in Power BI:

I’m sure it is an easy fix, and hopefully you can help me! If I missed a similar solution in your courses or blog please let me know!

[sam.mckay]

Check out the logic in this video. It should get you there, just small adjustments.

Let me know if need further help after this

[AmberM]

Thanks Sam! I reviewed your video, so let me struggle on this one for a bit. If I can’t figure it out in the next day, I will reach out for a “nudge” in the right direction!