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],
      VALUES(‘Date'[goback])
      , 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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s