- Ciao friends!
In this video I want to solve one calculation
that typically happens when you are doing budgeting.
I am not going to talk about budgeting a lot,
because if you are interested in budgeting,
we have a pattern on daxpatterns.com
that goes much deeper than I will ever be able
to do in a short YouTube video.
Instead, I want to focus on one calculation
that happens whenever you need to plot in the same chart,
the actuals and the forecast.
This is challenging because actuals and forecast,
they do have different timelines.
The actuals typically stop at some point today
because you don't have sales after today,
whereas forecast are available for the entire year.
Therefore, plotting them together in a chart,
or showing them in a matrix is challenging
because you need to pay attention to the dates
in order to provide meaningful results.
Let's start by looking together at the scenario.
I prepared a report
and here sales stop on the 15th of August.
You see that after August the value of sales amount
shows a blank.
Actually they end on the 15th of August
and I have the forecast for this year.
Forecast of course starts on the 1st of January,
and I also have forecast for the future.
I typically want to compute the year to date of sales
and the year to date of forecast.
And that is what I computed here
through two simple year to date calculations.
Year to date of sales amount at some point
shows the same value again and again
whereas the forecast goes on
because there are values available also for the future.
Now, this proves to be a problem whenever
instead of using a matrix, you plot data on a chart,
because the forecast still provides a good line,
whereas the sales amount is just ugly to look at.
You see what happens, this is the value
of the year to date of sales amount
that reaches the 15th of August,
and then it becomes a flat line
because it does no longer grow.
On the other hand, the year to date of the forecast
grows also in the future,
because we do have data for the future.
What I would like to obtain is a different visualization.
I would like to create a chart like this,
where I have combined in this first line
both the sales and the forecast.
I'm showing the actuals up to today
and then I'm extending the line
with the value of the forecast.
So this line that becomes flat is the year to date
of the sales amount,
the red dotted line is the combined calculation
and the black dotted line is still the forecast.
You will typically hide this line
because it's not interesting.
I still have it here because you see
that already in August the two values are split
because sales stop on the 15th of August
and therefore, already for the month of August,
the two values are different.
How do I compute such a value?
Well, in order to compute it,
what I need is to be able
to compute the remaining forecast that you can see here.
This is the same report as before,
but now I have the sales amount, the forecast as earlier,
but I have another measure remaining forecast.
Remaining forecast computes the value of the forecast
for the future.
So it only computes the forecast for dates after today.
And the thing is, what do we mean by today?
Today is the last date for which we have sales.
So in order to compute this number,
we will need to compute the last date
for which we have sales
and then compute the value of the forecast
only for the future.
The interesting part is that,
I can now add the two values together
and build the sales and forecast measure
that computes the actuals up to the point
where we have sales.
Already in August you see that this number is different
than the forecast, and it's different than the sales amount,
because it already combines together forecast and actuals.
Whereas in the future, it only shows the forecast.
So our goal will be computing the remaining forecast,
all the remaining part of the calculation
are absolutely simple to do.
And if you are interested in looking at more details,
you can go to SQLBI.com, download the article
and there you will find the file.
So we need to compute these remaining forecast
and we start with this model
where I already have the remaining forecast,
but we are going to rewrite it together
in order to understand step by step how it works.
Now, the first calculation, the first thing
that we need to do is compute the last date
for which we have sales.
Once we have this last date,
then we will compute the forecast after that date.
And finally, we will be able to combine everything together.
We already have the solution,
but I'm going to write a new one,
so let's start with a new measure,
we call it...
RF, remaining forecast,
and we first need a variable,
which is the last date with sales.
This can be as simple as MAX of sales order date.
Let's start returning the temporary values that we compute.
So we do that step by step,
and we are sure that there are no problems
in the calculation.
So let's just compute the last date with sales
and see how lucky we are with that.
But as you see, it is not correct.
It is showing the 29th of January in January
the 28th of February, the 31st of March in March.
Sometimes it's the end of the month,
sometimes it's a bit earlier than the end of the month.
And the reason is when I compute this MAX
that is happening in the filter context defined
by the matrix that contains January, February or March.
I need to get rid of that filter context
to obtain the last date ever.
So that needs to become CALCULATE MAX of sales order date,
and I can just use REMOVEFILTERS if I want to get rid
of any filter from the model
or remove filters from the date,
if I only want to get rid of the filter from the date.
Computing it this way,
now you see it returns the 15th of August everywhere.
So now that I have the date, the last date with sales,
I need to compute the forecast after that date.
Again, I need another VARIABLE
let's call it remaining forecast
and that can be CALCULATE the value of the forecast,
and then I apply a filter on the date
that need to be greater or equal the last date with sales.
If I did it correctly, this will produce the forecast
only after the last date.
Let's RETURN this as the result, it's not going to work,
but it's useful to see why it doesn't work.
I want to reduce that a bit,
so let me go back a bit and then we'll look at the code.
You see it returns the same value everywhere: 27,708.
Why is it doing that?
The reason is, this condition date date
greater than last date with sales,
overrides the filter context on the date.
And because date is marked as a date table,
it kills any filter on the table
and replaces it with this new filter.
What I want to do is combine this filter with the other one.
So I want to keep the filter on April, 2010,
and add this farther filter.
That's why I need KEEPFILTERS around it,
and if I use KEEPFILTER, now I have my calculation.
You see that it shows a blank everywhere up to July,
but starting from August, it shows a value.
Now going farther, it's only a matter of
summing this value with the forecast
and with that, you will be able
to build the chart exactly as it is
where I'm showing the year to date
of the sales amount combined with the forecast,
or just the year to date of the sales amount.
It is a simple calculation
that only requires basic understanding of CALCULATE
and the filter context.
As you have seen, once you have a solid understanding
of how the filter context works,
computing this calculation is straightforward.
Now, if you are interested in looking at more details,
go to SQLBI.com,
there you will find the article
and you will be able to download the file and play with it.
Just pay attention that the file contains some advanced
DAX code which is not explained in the article.
But if you want to understand more details about
how that code is computed, on daxpatterns.com
you will find a complete explanation
of how to build budgets with Power BI.
Enjoy DAX.