Day over Day Change Calculations DAX – Accounting for Weekends

Well it’s been a while between posts, thought I would share a little DAX formula I used to determine day over day changes. If you are asking why not use the built in PreviousDay Time Intelligence function, I did, however Mondays posed a bit of a problem previous day being Sunday and all.

How to Tackle this problem hmm?

  1. Add a calculated Column to your Date Dimension, I called mine GoBack:
    • IF(Weekday([altDateKey]) = 1, -2, -1)
      • if the weekday = 1 then go back 2 else go back 1
  2. Add New Measure to your Fact Table Previous Day Amount
    • Previous Day Amount:=IF(HASONEVALUE(‘Date'[GoBack]), CALCULATE([Amount], DATEADD(‘date'[altDateKey],
      , DAY)), BLANK())

      • If the table returned by Values function returns more than one row set value = null else calculate the amount filtered with DateAdd function
        • The Key is the Values function which returns the value of the [goback] calculated column for the selected date, this value is used as the second parameter in the DateAdd Function, negative integers let you go back to the future
  3. Add New Measures to express your day over day calcs ie:
    • D-o-D Change (%):=if(OR(ISBLANK([Previous Day Amount]), [Previous Day Amount] = 0), Blank(),[Amount] – [Previous Day Amount]) / [Previous Day Amount]

Hoping to Blog more in 2013