Adding 4/13/52 weeks of data but start points needs to move

[WilliamKaro]

Hi there,

I have a set of data where I need to calculate value based on a set amount of days. 

In the case of 4 weeks data, I've done this calc

Value 4 w/e = CALCULATE(SUM('UK Pricing Data (Appended)'[Value]),DATESINPERIOD('UK Pricing Data (Appended)'[DATE W/E],MAX('UK Pricing Data (Appended)'[DATE W/E]),-28,DAY))

Now when i calculate the 4 w/e LAST YEAR data i performed this calculation:

Value 4 w/e -1 = CALCULATE([Value 4 w/e],SAMEPERIODLASTYEAR('UK Pricing Data (Appended)'[DATE W/E]))

The data starts at 18 May 2024 (-28 days) but the data last year needs to start on the 20th May 2023 and because of this, it calculates the dates from 13th May meaning it's not the right number to compare. 

Is there a DAX rule to look at last years numbers but 2 days ahead of same period last year? Do i need to create a custom table to arrange what is defined as 4/13/52 weeks?

Thanks!

[lbendlin]

Go with multiples of 7 days - 28/91/364  . Much easier, impervious to leap years, and more "like for like".

[WilliamKaro]

Hey there the challenge i face is that the data starts 2 days before . So for instance 20th May 2024 wont extract data from the 18th. The data is also on a weekly basis. If this is the case, surely the solution you mention wouldn't work?

[lbendlin]

As long as you have a calendar table in your data model and your fact table has a date associated with the week number this will still work.