- Ciao friends.
In this video, I want to show you
how to count the number of working days
between two dates using DAX.
And as an example, we start from this report
that only shows year, month and sales amount.
I want to produce a new measure
that computes the average delivery time
in days of my orders.
Now, the delivery time expressed in days
is simple to compute
because all what I need to do
is go on the sales table,
and here I have the order date
and the delivery date.
If I subtract the order date
from the delivery date,
the result of this operation is
the number of days in between the two dates.
So this is quite simple.
I just create a new column,
let's call it delivery days,
which is the delivery date
minus the order date.
As a result, because it subtracts two dates,
it will be a date,
but I can convert it to an integer
and this is the number of days
between the two dates.
If I want to project this to a report,
I just need to build a measure
that averages this value.
So I create now a new measure,
let's call it average delivery days,
which is just the average of the delivery days.
And my report is already working,
I now have a number, this number produces eight days
... eight and a half days
as the average number of days.
But what if I want to compute the same value
but count the working days,
avoid using Saturdays, Sundays
or whatever vacation is in
between these two dates.
Now I can no longer just use a simple subtraction.
What I need to do is search in the date table
for the dates which are working days
and lies in between these two dates.
Again, I need to write a bit more DAX.
If we take a look at the date table,
I already have here,
a column, is working day
which is either true or false.
It is true in working days
and it is false in non working days.
The way you populate this column
is totally up to you.
This version is very simple,
you might need to handle vacations
or whatever kind of holidays
you have in your country.
But the presence of the column
is extremely important.
Because the algorithm we are gonna use
is to count the number of days in this table,
which are working days
and they happen to be in between the two dates.
So I can create again a new column
in the sales table.
And this time it will not be the delivery days
but the delivery working days.
So we start with a new column,
let's call it delivery working days.
Let's go with a longer but easier
to understand expression delivery working days.
How can I do that?
Well, I needed to count the number of rows
which are in the date table.
And I will apply a filter
that's why CALCULATE becomes useful.
I do a CALCULATE, and I COUNTROWS
the number of rows in the date table.
Date table.
Then I need to start applying filters.
The first filter is that I want
to restrict the date table
to only the dates that happened
to be between the order date
and the delivery date.
So I need to use DATESBETWEEN
the date, and I want to start from
the sales order date
and the sales delivery date.
Actually, I don't want
to stop at the delivery date,
I want to stop a day earlier
because DATESBETWEEN will include the boundaries.
So I need to use delivery date minus one.
If I stop the calculation at this point,
the result that we obtain
is exactly the same as the number of days.
It's just more complex,
but it will be nearly the same.
What I need to do is to add the further condition.
I want that day to be a working day.
And because I have the information
in the date table,
I can say, I want also
the date ISWORKINGDAY to be true.
There is still one thing to do,
because I'm using CALCULATE
and I am in a row context,
context transition is gonna happen
and I don't want the effect of context transition,
otherwise the filter from the order date
will be applied to the date table.
I don't want that to happen
so I will use ALL on the date
in order to get rid of
any effect of context transition,
and besides without the calculation.
If I do that the new column
will contain the number of working days.
You see that we have seven delivery dates
but out of these seven, only five are working days.
This is now my calculated column,
if I want to produce that in a report
exactly as I did earlier,
I need to create a new measure,
let's call it average working, then...
average delivery working days, delivery working days
which is just the AVERAGE of
the delivery working days in the sales table.
And once I put this calculation here,
you see that the delivery working days
are always smaller than the average working days.
It's not a complex calculation,
it's pretty simple
and it already shows you the power of DAX
when you use it in your reports.
Enjoy DAX!