As we want to compare by day by week and year, we had to use a formula to generate the unique number combination.
Create a column DWY = ([WeekDayNo]*100+[WeekNo])*10000+[Year] . This will create numbers like 5012015, 5012016, etc. etc
depending on the Date.
When we use the measure to compute the sales for same day same week last year as
SalesLYDWY = Calculate([Sales], Filter(ALL(Calendar),Calendar[DWY] = Max( Calendar[DWY]) -1 ))
it becomes simpler to just substract 1 from the current selected calendar year.
I have used your solution, it works great. The only issue is that now I am trying to aggregate it per month as well.
To get the sales for last year I am using this:
Sales Last Year = SUMX(VALUES('Calendar'[DWY]),Calculate(SUM('Sales & Tickets'[Sales]), Filter(ALL('Calendar'),Calendar[DWY] = MAX(Calendar[DWY])-1 )))
Unfortunately when drilling through from day to month data is slightly inaccurate. Any idea how to solve this issue?
Again, thank you for your previous solution, it was extremely helpful!