Cut out the noise - Analyse multi-year performance over time with Power BI & DAX

by EnterpriseDNA Regular Visitor on ‎02-15-2017 11:58 AM

In this article, we are going to deep dive into a range of techniques you can use to analyse information over time.


View Power BI report here


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.


Multi Year Performance Analysis.JPG



Here's a summary of some of the DAX techniques used in this dashboard.


- Moving Averages

- Time Intelligence

- Cumulative Totals

- Measure branching


Download the Power BI Desktop (pbix) file for this solution here


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.


Data model.JPG



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.


pic 1.png


 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.


pic 2.png


pic 3.png


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.

pic 4.png


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.



pic 5.png


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.


Like so...



pic 6.png


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.



pic 7.png



For the below chart I didn't need to create any additional calculations, as I already had them from earlier.



pic 8.png


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.



pic 10.png


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.


pic 11.png


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.


pic 12.png


 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.


pic 13.png


 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.


pic 14.png


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


Learn Power BI & DAX through downloading pre-built, advanced Power BI models


Learn more about Power BI at Enterprise Power BI User Group


by Hauke Frequent Visitor
on ‎02-20-2017 08:04 AM

Great and helpful article Smiley Happy

by Orokon Visitor
on ‎02-22-2017 05:42 PM

Download the Power BI Desktop (pbix) file for this solution here
pbix download link not working

Please upload it

by EnterpriseDNA Regular Visitor
on ‎03-01-2017 12:45 AM

Links are working fine