Forum Discussion

bo_afk's avatar
bo_afk
Post Patron
6 years ago
Solved

Comparison vs same week last year

I'm trying to compare the sales from this week this year, vs the same week last year (based on week number). Whilst I do have dates in my data, I can't use the SAMEPERIODLASTYEAR function since th...
Need Help
PaulDBrown's avatar
PaulDBrown
Community Champion
6 years ago

bo_afk 

One way to solve this is to add a period index in your date table for your week numbers, and the use this in the filter statement in your measure along the lines of.

Same week last year = CALCULATE([your measure],
FILTER(Date Table,
Date Table [period index] = SELECTEDVALUE(Date Table[period index]) -52))

 

For multiple selections use the MIN and MAX to establish the range.

EDIT: you might have to inlude a variable to cater for years with 53 week periods!

 

Here is an example:

YearWeek Table (I've actually created a new table) as in:

 

 

 

to set up the model like this:

 

And the build you measures and visuals referencing this YearWeek table, such as:

 

 

Sum of Forecast LY = 
VAR Weeksinyear = CALCULATE(MAX('YearWeek Table'[Week of Year]); 
                    ALLEXCEPT('YearWeek Table'; 'YearWeek Table'[Year]))
Return
CALCULATE([Sum Forecast];
FILTER(ALL('YearWeek Table');
'YearWeek Table'[YearWeek Index] = SELECTEDVALUE('YearWeek Table'[YearWeek Index]) - Weeksinyear))

 

 

 

To get you this: