Forum Discussion

jpereztang's avatar
jpereztang
Helper I
10 years ago

Weekday Last Year Comparison

Hi everybody !   I have a requeriment that i don't know how to achieve. Question is simple, how can I compare this year sales with exactly the same weekday sales of last year. take for instance, To...
Need Help
Tips and Tricks
Anonymous's avatar
Anonymous
Not applicable
10 years ago

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

 Column Labels             
 1 2 3 4 5 6 7 
Row LabelsSales2SWLYSales2SWLYSales2SWLYSales2SWLYSales2SWLYSales2SWLYSales2SWLY
2015              
24          41 43 
2545 47 49 51 53 55 57 
2659 61 63 65 67 69 71 
2773             
2015 Total177 108 112 116 120 165 171 
2016              
24           41 43
254145434745494751495351555357
265559576159636165636765696771
27697371 73         
2016 Total165 171 177 108 112 116 120 

 

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

VijayReddy's avatar
VijayReddy
Frequent Visitor
8 years ago

Hi CheenuSingh,

 

Just wondering why you would multiply WeekDayNo by 100? Please could you explain?

Thank you

Vijay

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi VijayReddy

     

    As we want to compare by day by week and year, we had to use a formula to generate the unique number combination.

    So,

    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.

     

    Cheers

     

    CheenuSing

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      This dax actually helps me  a  lot.. Now I can able to compare weekday with previous year same day. In my case I have to write a  dax for if both the years has same weekday in a month (sunday with sunday, monday with monday etc.,) then i'm supposed to assingn it as P, If any non comparable day in a month then it should asssign as "T". Also it will workout in the case of leap year.  Please help me with the  exact dax. TIA

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi, did you find a solution for your problem, I would be so happy if you did because I am struggling with the same problem! Thanks