Line charts in Power BI provide a way to help supplement a sales table with useful trend information. In this post, I’d like to take this a step further, and provide a dynamic moving average chart that allows the user to determine (as need be) the number of days to look back when displaying this visual.
By stretching out our moving average over a larger window of days (called a lag period), we are able to smooth out the line to suit our needs, and simply focus on the longer term direction.
Here are the steps I took, including some changes needed to account for leap years:
- Calculate the sales measure using SUMX to iterate over the sales table to multiply each row of Sales Quantity * Unit price.
2. Create a disconnected table that lists all possible lag periods available to the user. Simply choose Enter Data from the ribbon, and manually key in the list of numbers you want included in your lag periods. I named this table ‘Look Back Days‘, and loaded the table.
3. Create a slicer based on this new table, and make sure the slicer is set to ‘Single select’, only allowing the user to choose one item at a time. Our upcoming measures will depend on just a single selection being made to work as expected.
4. Lets write our current year moving average measure.
Note: I work with a non-standard fiscal calendar, and have always used my own modified date table. For custom calendars, I’ve just found this to be more flexible and have gotten into the habit of creating custom time intelligence functions as you’ll see below.
I use variables to step through the different calculations. I start by identifying the current date in my table. Note I am not interested in the MAX date, but simply the current date in my table. This will allow me to go back in time based on the user lag period selection for each day in the table.
Next, I reference our disconnected table ‘Look Back Days’ to identify the lag days the user has chosen. Using SELECTEDVALUE I am calling that single selection from the slicer. If there is no selection, my alternate value is 0 days back.
You’ll notice that I have this Look back time frame defined by the current date – begin date, but then use COUNTROWS to define my final denominator. The purpose of this is to protect the early months in my sales file from calculating misleading daily sales average results. I don’t want someone looking way back at day 15 of the file, and choosing a 60 day lag period, and expecting to see a reasonable daily sales average. 15 days of sales divided by 60 would be misleading.
My last consideration in this measure is to avoid an amount showing in a subtotal or grand total. I use HASONEVALUE to check for a single date in my table. If it does not see just one date, we are in a subtotal or total line, and the result returned is blank.
5. Correct for Leap Year: Unfortunately calculating the prior year moving average measure in this way is not just a copy/paste of the current year measure less 365 days. Leap years bump the prior year measure off track a day from the current day depending on the year we are currently in. To adjust, I change every February 29 in my custom date table (see below) to assume the same sequential day number as the day prior, February 28. Doing so essentially folds this extra leap year in 2012 in with the 28th when we are looking back a year from February 28, 2013.
By choosing the MAX date in _CURRDATE I know that my prior year measure result will respect the sales on February 29, 2012, even though it displays on the following year February 28, 2013. For purposes of this adjustment, the moving average prior year result for February 28, just rolls forward a day and assumes the average on February 29. This is not perfect, but close… We pull reasonable totals, and the good news is that our days stay on track for the remainder of the year.
I find it helpful to create a check tab that lets me compare two years. If my math is off, this has been a good way to figure out where the issue might be. Below you’ll see how adjusting my data table (day sequence) made it possible to fall back on February 29 numbers.
We now have the measures in place to build our visual. To do this, I will use the classic line chart visual in Power BI. I put my Date field from the calendar table in the Axis field. I then use both my current year and prior year moving average measures (CY / PY) in the values section of the chart. In both my table and chart i’ve colored CY as blue and PY as a light brown.
Next I choose a 60 day lag period to control how far the moving average calculation looks back in the measures.
The final result is a graph that trends current and prior year 60 day moving average on sales.
You’ll notice there are other lines on this chart. The white lines show the fluctuation of actual daily sales. The black line is a fixed smooth longer term 180 moving average for comparison. The blue line as mentioned is current year and the brown prior year.
When you compare the current year to the 180 lag period, we may be interested in how the current year interacts around the longer term black line. Any dip of the current year below the black line may be a tip for a possible continued downward trend, and vice versa.
Last but not least, I need to use the second Y axis… just because it’s there! For Y axis 2, I calculate the accumulated year-to-date percentage change over the prior year sales.
Click on the image above to see the updated report.
There is no doubt that there are other ways of creating visuals like this. I would be interested in hearing how you’ve created something similar. My hope in this post is that you’ve learned something about using variable, custom calendars or how to use a disconnected slicer.
If you are interested in the pbix used or have a question, please drop me a note at mark@storybi.com.
Author: Mark Walter
Leave a Reply