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
bo_afk's avatar
bo_afk
Post Patron
6 years ago

Hi v-alq-msft , thanks for your suggestion. 

 

I don't think this works for my data as the weeknum function within Power BI is slightly different to how we've defined the week number in our data. Since I already have the weeknum, and yearweek columns in my data, is there a way to reference this in the measure? The user is able to make multiple week selections in the slicer.

 

Example,

If the slicer selection is yearweek 202005 - 202007, the measure will then take the values from a year ago, i.e. 201905 - 201907

Note: there is a many-to-one relationship between the calendar(date) table with the sales table as there is also a market column in the sales table so a date will appear on multiple rows for each market.

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: