In this article, we are going to deep dive into a range of techniques you can use to analyse information over time.
I find DAX so effective with this type of analysis. Being able to run all these calculations virtually within measures is far more efficient than back when I was predominantly using excel and having to create loads of supporting columns.
First off, let's set the scene. We are looking at data from an organisation that sell products to a number of different regions.
I wanted to analyse how performance has been going this year, but also see how it has gone last year and the year before that. I wanted to see if there was any seasonality in the performance or trends I could identify.
One of my other goals here was that I want to be able to dynamically jump into reviewing how performance has gone against a number of different dimensions like customers, regions, sales channels etc.
This was the end result.
Here's a summary of some of the DAX techniques used in this dashboard.
- Moving Averages
- Time Intelligence
- Cumulative Totals
- Measure branching
Let's go through these so you are able to see how you can use them in your own analysis.
Firstly, let's check out the data model so you can see what we're working with.
So we have our lookup tables up the top with one-to-many relationships down to our fact table. We will be looking to run our filters from our lookup tables, but then run our aggregations through DAX on the fact table.
First of all, let's start simple. We need to develop a core measure like total sales. We can then branch out from this in many different ways with some patterns of DAX code.
We can do a lot from just this one measure!
The first thing we need to do is look to run the same aggregation for prior years but align it to the current date context so we are able to review the information side by side.
The only way to do this is with CALCULATE.
So we need to find sales for last year and the year before. You are able to do that with the following formula.
Not too difficult. Key here is understanding how to change the context of a calculation with CALCULATE.
So now we are able to see all the sales information against the most recent date context.
Now I felt that it would be way easier identify any trends in this information if I smoothed it somewhat. So I decided to calculate moving average of all these measures.
To create a moving average you can use this DAX formula.
So now I have the 1 month moving average for current sales. Now to get this for historic periods all I needed to change was the expression within the AVERAGEX function.
I did exactly the same for the sales 2 yrs ago.
So now I have moving average for this year, last year and the year before that. What is so great, if that they are all now in the same context so I can analyse then on top of each other within a visual....and they are all still connected to the rest of the data model so I'm able to filter the visual by any of my lookup tables.
For the below chart I didn't need to create any additional calculations, as I already had them from earlier.
I also wanted to review this information cumulatively. I always find the pattern recognition and trend analysis work well with these cumulative total visuals.
To obtain the cumulative total I used a combination of CALCULATE, FILTER & ALLSELECTED functions.
Same as above, to be able to review the last year and 2 yrs ago sales cumulatively in the same date context all I needed to do was to change over the 'total sales' measure.
Now I had the makings for my cumulative chart.
Viewing data cumulatively over time, with the ability to dynamically change the information you're are viewing via slicers is a powerful way to quick find compelling insight in my view.
We've covered a number of bases now for viewing how performance has tracked over time.
The dataset we have used here only runs for a couple of years, but I can imagine this being a powerful piece of analysis if you have longer term data.
I lastly wanted to be able to gain some quick insights into city and sales channel metrics.
Reviewing the difference between this year's sales versus last year is an easy step from all the current measures that have already been created.
We are just branching out from our initial measures and then referencing them within another measure.
With this measure we are able to align it to the city context (dimension) and see, what are the best and worse performing regions very effectively.
We can even use this visual to filter our other charts which again bring another element to our analysis.
We've covered a number of techniques quite quickly here.
As a quick summary, these are the key takeaways.
- Start with your core measure (eg. 'total sales') which can be for sales, costs, profits etc.
- Create measures that analyse different time periods but enable you to view them in the current date context.
- Re-use those measures inside some patterns of DAX code like moving averages and cumulative totals
- Utilise the power of the data model to be able to filter the calculations through a number of dimensions.
Good luck with these techniques.
Sam McKay. CFA
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.