- Ciao, friends.
In this video, I want to show you
how to compute a rolling average over the last 12 months.
Rolling averages are very commonly used
whenever you have a measure
that has a strong variation over time.
If you plot this measure on a chart,
then you will see highs and lows in the measure
and it is hard to understand
the overall trend of the measure.
By replacing the measure with its rolling average,
let's say over the last year,
you obtain a much smoother line
that makes it easier to understand
the overall trend of the measure.
As a bonus, before the end of the video,
we will also put the same calculation
inside a calculation group
in order to make it easy for users
to create any kind of average or any kind of measure.
Let's get started.
First, we need to understand
the calculation we want to obtain.
And to show you that, I created this chart.
Right now this chart is just showing the value
of the sales amount by month
and you see that it changes quite hardly.
So I have a peak here, I have a drop here,
I have another peak here and it is hard
to understand the overall trend.
But I can add to this chart not only the value
but also the moving average over the last 12 months.
And you see that now with the darker line
it is much easier to follow the trend of the sales amount
because I do no longer feel the drops and the peaks
but I show a line that goes much smoother.
Now, we will compute first the moving average
and then we will also create a calculation group
to compute the moving average.
We will be able to build different moving averages
and apply them to different values.
Right now, this report is computing the moving average
of the margin but I can just replace the margin
and put the sales amount
and I will have the different moving averages
for the sales amount.
Now, I could just show you the code and comment it a bit
but I know how I like to do my videos.
I prefer to start with a model
that does not contain the solution
and then we build the solution step by step together.
Let's get started.
I need to get rid of this and start with a model
that does not contain the solution yet.
We need to solve two problems.
First, we need to compute the average
and then we need to make it move over time.
First, we start by computing an average.
Right now, my sales amount here is showing the value
of the sales amount and I want to add a new measure
that shows the monthly average of the sales amount.
That is not hard.
I just need to create a new measure.
I already call it sales,
the rolling average over the last 12 months.
And in order to compute an average,
I can simply iterate over the months,
so I use VALUES of date
calendar year month number,
calendar year month number.
And I average the sales amount.
This measure computes the average of the sales amount.
Now, on any given month,
it will produce the same value as the sales amount
but at the year level, it produces an average.
You see that month by month, the values are identical
but at the year level, sales amount shows 11 million
that is the sum of all the sales,
whereas my measure right now shows the average.
The thing is, right now, the average is being computed
only at the year level.
I want a similar calculation to happen for every month.
Now, if you focus on one month,
average computes the same value as the sales amount
because there is only one month selected.
So we need to extend the filter context
of every month, so to include a moving average
over the last 12 months.
There is a specific function
that let me extend the filter context
to include a longer period,
which is DATESINPERIOD.
DATESINPERIOD returns a set of dates
in a given period and you need to specify the period.
In order to specify the period,
you need a reference date,
so the starting point of the period
and then the duration of the period.
As a starting point,
we will use the end of the current period.
So whenever a month is selected,
we use as the reference date,
the last date in the current period.
And then we extend, starting from the last date,
we go back 12 months, three months, six months,
whatever period we want.
And to define the period, we need two values:
the length of the period and the unit of measure
because we can extend the period of one month,
three days, one quarter, so it's always a number
and its unit of measure.
Let's do that.
We have our sales rolling total
and we want to use DATESINPERIOD.
In order to use DATESINPERIOD, we need the reference date.
So because we need to debug the measure,
let me start using variables.
This will be the result.
And then I need to RETURN the result.
Right now, I just use a variable
in order to be able to add further values.
And let's start with the reference date.
As I said, the reference date is the last visible date.
So let's use a
last selected date,
which is just the maximum of the date column.
And let me return the last selected date,
so we see the actual result.
Last selected date in January is the end of January.
In March, it's the end of March.
In September, it's the end of September.
So right now, my last selected date
is always the last date in the month.
Now I can use DATESINPERIOD.
I can create the period to use.
And I use DATESINPERIOD.
DATESINPERIOD requires me to specify the column
that needs to be the date column in the date table.
The reference date, which is the last selected date.
I could write -12 to go back
and then I need to specify the unit of measure, MONTH.
Since we strive for perfection,
instead of using -12, let's declare another variable,
which we call number of months
so that if we need to change it later,
it will be easier.
A bit smaller.
Okay. How can I check the content of period?
Well, I cannot return period
because it's a table but I can RETURN COUNTROWS of period.
And you see, it shows at the beginning a smaller number
because we are at the beginning of time
and then it's always 365 or 366,
depending on whether a year is a leap year or not.
So my DATESINPERIOD contains the dates
in the period that I'm interested in.
Next, I need to change the way I compute the result.
I no longer want the average of the given month,
I want the average over the entire period
and this is where CALCULATE becomes useful.
I use CALCULATE to perform the same average
that I was doing earlier but this time, in the given period.
And now I can RETURN result.
I change the formatting,
so I need to restore that, two decimal places
and commas.
Okay, now you see, for the first month,
it shows the value of January.
On the second month, instead of showing the sum,
it shows the average of January and February
and this goes on until I reach 12 months.
And at that point, it always returns the average.
At the year level, it's the average
but also month by month,
it no longer shows the value of the sales amount
but the average over the last 12 months.
That measure is fine, it works nicely.
It has a problem here because
until I have sales, it works fine
but then it also shows values for 2010.
The thing is, I don't have sales in 2010.
But even though I don't have sales,
the moving average over the previous 12 months has a value.
I need to get rid of this number
and I have a couple of options of doing that.
Either I check for the presence of sales
or I optimize it and make it a bit faster.
Let's do that.
In order to get rid of values in 2010,
I can do a first check.
I just check that there are actually sales,
IF sales amount is greater than zero,
then I return the result, and that works.
You see that 2010 is no longer visible here.
I only have data for 2009.
But it's not optimal because
it requires me to compute sales amount again
and this requires a scan of the sales table.
A better way of obtaining the result
is to check if the filter context
contains the last visible date.
So I can compute the last date,
no, the last date with sales actually,
which is the maximum of sales order date.
Then I need the first visible date,
which is the minimum of date date
and then instead of using this condition,
I check that the first visible date,
first visible date is less
or equal the last date with sales.
Last date with sales.
This condition is a bit faster
because it does not require me
to compute the sales amount
but only to scan the fact table
to retrieve the last order date.
It's just a MIN, and this MIN is working on the date table.
So it's likely to be a bit faster.
Obtaining the very same goal as before.
So I do no longer show data in 2010
and I avoid scanning the fact table multiple times.
Now that I have the running total, I'm happy
and the code is already working.
But we can do something better than that
because as soon as you build the first rolling average,
then users start to ask,
"Hey, I'd like to see the rolling average
"over the last three months,
"six months, 13 months, whatever period.
"And beside, I do not want only the rolling average
"of the sales amount, I might want also the rolling average
"of the quantity, of the margin
"or whatever measure comes to my mind."
To do that, the best option is to create a calculation group
that applies the same modification
so the same average over a period
to whatever measure has been created in the report.
Let's do that with a calculation group.
If we want to create calculation groups,
so we cannot do that from inside Power BI,
we need to launch Tabular Editor.
So let's start with Tabular Editor
and we create a calculation group
that we call rolling averages where we start
with the rolling average over 12 months.
So let's create a new calculation group
that we call rolling average,
that contains a column
that we call rolling average.
And then we create the first calculation item
that we call rolling
average 12 months.
The code is the same as my measure.
So I go to sales, take the code of the measure.
Let me increase the font.
And copy it here.
The only change that I need to do
is replace this sales amount with SELECTEDMEASURE
because being a calculation item,
now it works with whatever measure.
So I cannot use sales amount,
I need to use just SELECTEDMEASURE.
And while we are here,
we create also the rolling average over three months.
Rolling average three months.
This is the same code as the 12 months
but I use 3 instead of using 12.
And this is the only change
that I need to do to make that work.
Now I can save that.
Go back to Power BI Desktop, refresh it
and I have my rolling average calculation group now.
I can get rid of the measure.
I only have the sales amount, I use the rolling average
and you see that I have rolling average 12 months
and three months.
The rolling average over three months
has a stronger variation than the one over the 12 months.
I still need to fix the sort order
because I want three months to be before the 12 months.
Save it again, refresh again
and now data is sorted correctly.
I have three months, 12 months
and everything is working fine.
I can do the same on the chart.
I have the sales amount by calendar year month
but now I can take my calculation group,
rolling average, add it to the legend
and you see that in the same chart,
I have the rolling average over three months
and the rolling average over 12 months.
I am missing the sales amount.
That is why in the calculation group,
it's always a good practice adding also
a dummy calculation item
that just returns the SELECTEDMEASURE.
I call it value, I put it first.
Save everything, refresh it.
So now I have value, which is the first.
Then I have the rolling average over three months
and the rolling average over 12 months.
Then it's likely I want to apply a filter.
I only want value and rolling average over 12 months.
And I obtain the same chart
that we have seen at the beginning of the video.
As you have seen, creating a rolling average is not complex.
All you need to have is a basic understanding of CALCULATE,
filter context and how to use DATESINPERIOD
to extend the filter context to the desired length.
Then by mixing this calculation with calculation groups,
you really obtain powerful reports.
If you are interested in this topic, go to sqlbi.com.
There you will find the article. The link is below.
And you can also download the file, play with it
and learn more about DAX.
Enjoy DAX.