Level: Intermediate

Last week I attended the 2016 PASS Business Analytics Conference in San Jose.  I attended my first BA Conference last year and had a blast – this year was equally as good.   However I had a really interesting week in the lead up to this year’s conference.  As you may have read last week, Rob had a mishap a couple of weeks ago that has left him less mobile than he would like.  Seriously, I have seen the photos of the “temporary realignment” of his leg and I didn’t eat for 3 days after – it kinda looked (a lot) like this.

Rob Leg 2

Anyway, I got a call last week from Rob asking if I would fill in for him at the PASS BA Pre-Conference session that he and Avi were scheduled to deliver.  I was planning to be in San Jose 1 day early anyway so the timing was good.  Besides, I would never miss an opportunity to present on the topic of DAX.  I thought today I would share with you one of the topics I covered last week, which is a workbook that demonstrates a custom time intelligence pattern for smoothing out your data.  This is not the most complex topic you will find about DAX on the Web.  But what I learnt from talking to people at PASS BA Conference last week is that there are many people that would like to see more simple but practical “worked through examples” to read, study and learn from. I am definitely planning to take this feedback on board and share more practical worked through examples of real life scenarios in the future, but now on to today’s topic.

Average Monthly Sales Rolling Quarter

I have used Adventure Works to produce a simple line chart in Power BI showing total sales by month over time.  The raw chart looks like this.

image

You can download the workbook here if you want to take a look, or simply look at the embedded version I have pinned at the bottom of the post.

Notice the spikes in sales in different months in the chart above?  These spikes are very common in sales data, and in my experience they can be even more prevalent in weekly sales data.  These spikes make it difficult to analyse trends in the data.  You could put a trend line into the chart (thanks to the April update do Power BI), but a standard linear trend line is too simplistic to really see what is happening in your data, particularly if there are seasonal changes.

One good way to look at the trends in your data is to add an Average Monthly Sales Rolling Quarter trend line to the chart.  You simply take the total sales of the last 3 months and then divide by 3.  If you were doing a weekly trend, take the last 13 weeks and divide by 13.  When you overlay this Avg Monthly Sales RQ line on the original chart, it looks like this.

image

Notice how the black “trend line” provides a “smoother” view of what is happening in the data? The longer the time horizon of your rolling period, the smoother the curve will appear.  This next image is for Average Monthly Sales Rolling 6 months.

image

The rest of this blog will cover how to produce a report like this.

Preparing the Calendar Table

The first thing I did was to add a unique monthly ID in the form YYYYMM plus an integer ID column to my calendar table. It is not possible (in Power BI) to nest the Year Column and the Month Column onto the X axis, so you need to create a column for this.  One of the many things I love about Power BI Desktop is the tight integration of Power Query with Power Pivot.  If I was doing this in Excel, I would probably just write a Calculated Column in DAX, but the tight integration with Power Query makes it just as easy (maybe easier actually) to write the column in Power Query.

Add a Unique Month Name

I clicked on “edit queries”, clicked, “Add Custom Columns” and then inserted the following code.

image

The “times by 100” simply shifts the year across 2 digit registers making room for the month.  Then I can simply add the month number to the “year x 100” to give me a unique month description in the format YYYYMM.

Add a Unique ID column

The next thing I did was to add a unique Integer ID column for months.  Notice how the integer column simply starts at 1 and then increments by 1 for every period (month in this case) without ever resetting.

image

An Integer ID column is invaluable when you come to write custom time intelligence (see the formulas and explanation further down in this post), and I highly recommend you add one for every period in your calendar table that you want to use in your time intelligence patterns (one of the many things I learnt from Rob).  You can read all of my tips for creating a good calendar table from my Knowledge Base here.

My PQL code (Power Query Language – pronounced Peequel) for this ID column is shown below.

image

First I subtracted 2000 from the year.  Given my calendar started in 2001, this had the effect of giving me 1 for the first year, 2 for the second year etc.  I then multiplied this number by 12 so that when I added the month number, I ended up with a totally unique ID.  After doing this, the first ID number was 19 (2001 converted to 1, times 12 + 7 (July) = 19).  I could have left 19 as being the first number and it would all be fine.  But to tidy it up a bit, I simply subtracted 18 from the formula to force the ID column to start at 1.  I couldn’t simply insert an ID column from the menu because the calendar table is at the day level.

Rename your Steps

One more thing I did was to rename the significant steps in the Power Query pane.

image

Notice how much easier it is to see what you did when it says “Added YYMM column”, “Added Unique ID” compared to “Added Custom”, “Added Custom 1” etc?  The easiest way to rename the columns is to simply right click the step and then click “Rename”.

image

OK.,  then I clicked Close and Apply, then on to the DAX.

Average Sales Rolling Quarter DAX Formula

Here is my custom time intelligence formula for Average Sales Rolling Quarter.

image

Edit: Actually, rather than dividing by 3, a better approach is to divide by the distinct count of months.  That way if it is the first period ever, it will divide by 1 instead of 3 etc.

revised

The FILTER function in this formula does 3 things.

  1. It removes all filters that have been applied in the visual with the ALL(Calendar) portion of the formula.  If you think about it, this is a mandatory step.  The line chart filters the data model so that only 1 month is displayed for each point on the chart.  Given the table is filtered for a single month, it would be impossible to go back in time and fetch 2 more months and add them to the data table unless the filter is removed first.
  2. It then “reads” the current filter context from the data point with the MAX(Calendar[MonthID]) portion of the formula.  Any time you see an aggregation function inside a FILTER function like this, you need to simply realise that the aggregator is “reading the current filter context”.  I could have used any aggregating function for this “reading the current filter context” portion of the formula (for this specific chart) and it would still work.  The reason I used MAX for the first part and MIN for the second part is that this makes the formula more usable in other parts of the workbook.  If you had an instance where 2 months were selected in the filter context and aggregated into a single value (say in a Matrix), the MIN function would detect the first month and the MAX function would detect the last month in that filter context.
  3. The last thing the formula does is to reapply new filters to the table.  Whenever you see a naked column ( Calendar[MonthID] in this case ), then think about the underlying table in the data model.  Notice how I am using the unique integer ID column to “step back in time”.  No matter where I am in the Calendar table, I can always subtract 2 from the ID in the current filter context to fetch the time period that was “2 months ago” and so on.  Even though it is the YYYYMM column that is in the visualisation, you can refer to any of the columns in the table – in this sense you should learn to “think in tables” of data.   When the table is filtered, the YYYYMM value is visible in the chart, but the entire row is “visible” in the underlying data model – including the ID column.  You can use this to your advantage and use this ID column instead of the YYYYMM column in your formulas.  There would be no point subtracting 2 from the YYYYMM column as it wont work when you cross year end periods.

The FILTER function above returns a “filtered” table and passes that table of filtered values to CALCULATE.  CALCULATE then propagates the new filter across to the connected tables (Sales in this case) and then “re-calculates” the measure [Total Sales] with this new filter context from the newly filtered data model.

I have extensive coverage, explanation and practice examples on how to write custom time intelligence functions in my book http://xbi.com.au/learndax if you are looking for a more comprehensive coverage.

Now for Something More Funky

Wouldn’t it be neat if I could let the user set the time horizon for the rolling average monthly period?  I created a disconnected slicer to accept input from the user on how many periods they want to go back in time.  The end user experience is really engaging, don’t you think?

time horizon slicer

Creating the Disconnected Slicer

I created the disconnected table by simply using the “Enter Data” option in Power BI.  I named the table TimeHorizon and manually added the values 2 through 6 into the table.

image

Write a Harvester Measure

The next step is to write a harvester measure to “harvest” the selection from the user.  The objective here is to capture the value selected in the slicer by the user.  It would be nice to be able to write the following formula:

Selected Time Wrong = TimeHorizon[Time Horizon]

But do you see what is wrong with this measure?  The measure has a “naked column”, and that is not allowed unless the function has a row context. If there is no row context, then you cannot use a naked column.  So what to do?

One possible correct formula for the harvester measure is as follows:

Selected Time = MAX(TimeHorizon[Time Horizon])

To understand how and why this measure works, you need to think about the underlying table and “imagine” what is happening to it behind the scenes when the user clicks on the slicer.  What happens is the slicer is filtering the underlying table.  You can’t “see” it being filtered, but indeed it is being filtered.  So when the user clicks “2”, the underlying table is filtered with only 1 row showing – the row with the value 2 of course is still “visible”.  So now that only a single row in the table is “visible”, what is the maximum visible value in the column?  Well it is 2 of course!  In fact the minimum value is 2, the average value is 2, the sum of all visible values is 2 and so on.  So when you write a harvester measure like this, you can use any aggregation function to “extract” the single value that has been selected from the slicer by the user, and this trick therefore gets around the “naked column” issue mentioned above.

The last step is to add a slicer to the visual from the TimeHorizon table, and then wow your users with a great interactive report.  Here is an embedded version of the workbook using the Power BI public sharing service.  You can read about Power BI public sharing here.

If you have a business scenario that you would like to see as a worked through example, add a comment below and I will consider it for a future blog post.

  Subscribe to PowerPivotPro!
X

Subscribe

Matt Allington

Matt Allington is a Microsoft MVP specalising in Power Pivot, Power BI and Power Query Consulting and Training based in Sydney Australia. Visit Matt's blog here. 

This Post Has 17 Comments

  1. Hi Matt,
    I think there is a part missing. There has to be an new Sales measure that includes the “Harvest Measure” instead of the fixed month set-off.

    Kind regards
    Christian

    1. Christian. Yes, this is in the workbook but I did not explicitly talk about it in the post. If you download the workbook you will see it. Better is to divide by DISTINCTCOUNT of the months so it automatically works in all instances.

  2. Very cool! I just did something similar but did not use the disconnected table to choose the period. I like that. The only difference I made was add a little more to the rolling avg measure where it looks for completed period before dividing by 3. So if there is only 1 month available, it won’t divide that by 3 for the average. But your disconnected table will be stolen by me and used in all future Rolling Average analysis! Thanks!

  3. Matt,

    Excellent post! Best take-away for me also was seeing how to manually create a disconnected table in Power BI.

    Also, many thanks to you and Avi for making a great success of the dueling dashboards session at Pass Business Analytics! Was just going over my notes last night..

  4. The first part of the graph is inaccurate because you’re dividing by 3 even when there is only 1 or 2 months of data. This inaccuracy is even more apparent in the moving graph above, because as you click from “2 months rolling” to higher numbers, it takes the rolling average line longer and longer to “catch up” to the original line. Notice, when you start with 2, it takes the rolling average line 2 months to catch up to the other line. When you click to 3 months, it takes the rolling average line 3 months to catch up to the other line. And so on, until you click 6 months, and it takes 6 months for the rolling average to catch up to the other line. The way to avoid this would be, ideally, to start your graph where you have sufficient data, or, at minimum, replace the hard-coded integer denominator with a more dynamic count of the months where data exists.

    1. Your right Brian. In my haste I missed one trick I normally use (normally not until I notice it like you did. One option is to do as you suggest, however you can also divide by the distinct count of the months – that way it will handle it correctly.

  5. Matt,

    I’ve read with keenness the post and I must say it has been thought through. Particularly, the concept of the disconnected slicer is a killer one which I shall be exploring more. Say for instance use it for selecting top/bottom ‘x’ items among other applications.
    I’m not advanced in DAX but I’m learning day by day. Going through the formula that adds ‘a unique month ID’, my question to you or the readers here would be -is there a way one can leverage on the data available to make the ’18’ being deducted dynamic? This is because when the earliest month is not 7 as is now, the 18 needs to be adjusted

  6. There are lots of ways to add an ID column to your calendar table. The only requirement is that it is an integer column and that it increments by 1 for each object (consecutive month in this case). In my example, my formula started at 19 (as a result of my calculation) and I subtracted 18 to make the ID column commence at 1. But there is no requirement for the column to start at 1. There is no requirement to know “which ID” is the first one. It is only used to make relative references, such as “previous month” (ID – 1), “same month prior year” (ID -12).

  7. Hello there! I am such a long time fan of you and Rob and the (can I hashtag?) #Power Movement. . . I have a dilemma and believe you guys can help me (please x 10^BI!). Let me start by saying I have used PowerPivot to create the data model and load to Power BI because there are features I need in PowerView that aren’t in Power BI-so yeah a huge fan of both. Anyway..eyes on the crisis!!!: I have to create a product profile ‘scorecard’ if you will that lists the product, Product Type, Category, Launch Date, Life-to-Date sales, Avg Mo Sales, Avg Qtr Sales, and Avg Yearly Sales, and last 6-mo sales in ROW FORMAT (AAAH!) I will also need a picture of the product to the right of the data (but think I have figured this one out. The purpose of this type of application is to give the Product Managers a high-level insight to like products so that when they are forecasting, they can filter the main product and then select up to 4 additional products for comparison. Additionally, I would use the example in Matt’s post to create a chart that has the actuals against the future’s forecast that is already locked in. (I believe I may need a ‘forward looking’ calendar for this?) I know you guys have invaluable knowledge but I beg please help me, I have bought every book you have and have racked my brain for nearly 2-months to do this. I think If you helped me I would begin to really understand the technology and run with it.

  8. This question really belongs in a forum. Rob, Mr Excel, Power BI and I all have Power Pivot Forums that can help. Just make sure you create a sample workbook and expla where you are stuck.

Leave a Comment or Question