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


#1

Hi Sam,

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:
StaticToplineExampleL4weekspowerbi

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!


#2

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

Let me know if need further help after this


#3

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!