- Ciao friends.
In this video, I want to show you
how to compute a running total with DAX.
But, first of all, what is a running total?
Here I have a report that shows
the year, the month, the sales amount
and then a classical year-to-date.
What does year-to-date do?
Year-to-date computes a running total in the year.
Indeed, it will reset at the end of every year.
You see that it starts growing
until it reaches the end of the year.
It reaches its maximum value,
and then it resets again and starts counting again.
Year-to-date has a specific DAX function to compute it.
What if I want to compute a running total?
A running total starts the same way as a year-to-date
but it does not reset at the end of the year.
There is no function to compute a running total in DAX.
So, we need to author it by ourself.
And, in order to do that, we need to think
before writing it out, the logic that we want to implement.
What I want to do is, for every row of the report,
I want to compute the sum of the sales amount
for all the dates that happen to be before
the last visible date in the current context.
So, if you are in August, what I want to compute
is the sum of all dates before August.
With that idea in mind, we can start writing code.
The running total requires you to use CALCULATE.
So, we start with new measure,
and we call it Sales Running Total.
First, we need to understand what is the last date
in the current filter context.
To do that, we store it in a variable,
we call it MaxDate, which is just the maximum
of the date column, date date.
Now, MaxDate contains the last date
that is visible in the context.
Then, I will need to use CALCULATE
in order to compute the sales amount
for all the dates before this date.
So, I can create straight my new variable result
that uses CALCULATE.
It computes just sales amount, or whatever measure
I'm interested in compute a running total on.
And then, I apply a filter that the date need
to be less or equal than the MaxDate.
I also get rid of any filter on the date.
So, what I'm saying is that I want
to compute the sales amount for all the dates
before the last date, ALL the dates before the last date.
Once I wrote it, I have an error which is,
oh I'm missing the return part.
I created a variable result, I just need
to add RETURN, Result.
Much better, now I format it as a decimal number.
Finally, I put it in the report
to see whether it's working or not.
And, you can see that, it starts the same way
as a year-to-date but then, when it reaches the next year,
year-to-date resets and start counting again.
Whereas our running total goes on computing the value.
It's a simple function that proves to be extremely useful.
Beside, it's worth noting that,
when we speak about running total
we quite always think of dates,
because running total works very well with dates.
But you can implement a very similar logic
whenever you have an attribute
that can be sorted in some way.
Imagine, for example, that all of
your customers have a customer class.
It can be Platinum, Gold or Silver.
And you want to compute a running total on top of it.
So, you want to see the sales of Platinum,
the sales of Platinum plus Gold,
the sales of Platinum plus Gold, plus Silver.
We are not gonna use alphabetical sorting.
We have the customer class code that defines the sorting.
And because we have a sorting,
we can implement a comparison operator.
And we use the very same pattern to implement this feature.
Indeed, if we take a look at the code
of running total sales customer
you see that it's nearly the same code.
This time, instead of using MAX we use SELECTEDVALUE
because we have only one value available,
both MAX and SELECTEDVALUES would return the same result.
Whereas in the date, MAX was the right thing
because we had multiple dates selected,
here we have only one, so we take
the class number, which is currently visible
and we compute the sales amount
for all the class number which happen
to be before the current class number.
And then, we get rid of any filter
from the customer class exactly as we did earlier.
So, running total is a common technique,
pretty useful to author your DAX code.
Enjoy DAX!