Weekday Last Year Comparison

jpereztang

One solution that came to mind is on the date (calendar) table

1. create a  column WeekNo= Weeknum([Calendar[FullDate])   ( Full Date is the Date column in DD/MM/YYYY format)

2. Create a column WeekDayNo = WeekDay(Calendar([FullDate])

3. Create a column Year = Year(Calendar[FullDate])

4. Create a column DWY = ([WeekDayNo]*100+[WeekNo])*10000+[Year] . This will create numbers like 5012015, 5012016, etc. etc

    depending on the Date.

5. Join your fact table to the Calendar table

6. Create a measure sya for Sales = sum(Facttable[SalesAmount])

7. Create a 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 ))

8. You should now be able to view the Sales and SalesLYDWY side by side.

Sample of what I did

This should work for all years and weeks for eternity.

If this solves your requirement please accept this as a solution and also give KUDOS.

Cheers

CheenuSing