DAX & The Month-over-Month Delta

 

There’s a few blogs out there which try to explain the problem and solutions.  I looked at them and found that they were not always the easiest to wrap your head around.  So, here is my solution which is hopefully a bit easier to understand and implement in your environment if needed.

The Business Problem: I want to calculate Month-over-Month (MoM) performance for online webinars

Approach:  I know how many attendees attended webinars in a given month and therefore to work out the delta between Month 1 and Month 2 is simple subtraction.  The problem is that the monthly attendee value is calculated by a COUNTROW function, surfaced by its own measure (Session Attendees).  So, how do I keep this dynamic with the minimum amount of formulas?

Solution:

1.  Create a new measure to show the Previous Month value:

PreviousMonthMeasure = CALCULATE([SessionAttendeesMeasure],PARALLELPERIOD(SessionDate,-1,MONTH

2.  Create a measure to calculate the delta:

MoMDeltaMeasure = SessionAttendeesMeasure – PreviousMonthMeasure

3.  To calculate the delta percentage, create a final measure

MoMPerformanceMeasure = MoMDeltaMeasure/PreviousMonthMeasure

(make sure you set this in the modeling as a percentage value)

In summary, your table should look something like this:

Month Attendees

(SessionAttendeesMeasure)

Previous Month

(PreviousMonthMeasure)

Difference MoM

(MoMDeltaMeasure)

Performance +/- MoM

(MoMPerformanceMeasure)

July 100 0 100 100%
August 150 100 50 50%
September 156 150 6 4

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 )

Google+ photo

You are commenting using your Google+ 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 )

w

Connecting to %s