馃攳
Counting working days in DAX - YouTube
Channel: SQLBI
[0]
- Ciao friends.
[2]
In this video, I want to show you
[4]
how to count the number of working days
[6]
between two dates using DAX.
[8]
And as an example, we
start from this report
[10]
that only shows year,
month and sales amount.
[14]
I want to produce a new measure
[15]
that computes the average delivery time
[18]
in days of my orders.
[21]
Now, the delivery time expressed in days
[23]
is simple to compute
[25]
because all what I need to do
[26]
is go on the sales table,
[28]
and here I have the order date
[30]
and the delivery date.
[32]
If I subtract the order date
[33]
from the delivery date,
[35]
the result of this operation is
[37]
the number of days in
between the two dates.
[40]
So this is quite simple.
[41]
I just create a new column,
[44]
let's call it delivery days,
[46]
which is the delivery date
[49]
minus the order date.
[52]
As a result, because
it subtracts two dates,
[54]
it will be a date,
[55]
but I can convert it to an integer
[57]
and this is the number of days
[60]
between the two dates.
[63]
If I want to project this to a report,
[65]
I just need to build a measure
[67]
that averages this value.
[68]
So I create now a new measure,
[72]
let's call it average delivery days,
[75]
which is just the average
of the delivery days.
[83]
And my report is already working,
[84]
I now have a number, this
number produces eight days
[88]
... eight and a half days
[89]
as the average number of days.
[92]
But what if I want to
compute the same value
[95]
but count the working days,
[97]
avoid using Saturdays, Sundays
[99]
or whatever vacation is in
[102]
between these two dates.
[104]
Now I can no longer just
use a simple subtraction.
[106]
What I need to do is
search in the date table
[110]
for the dates which are working days
[112]
and lies in between these two dates.
[115]
Again, I need to write a bit more DAX.
[119]
If we take a look at the date table,
[121]
I already have here,
[124]
a column, is working day
[126]
which is either true or false.
[128]
It is true in working days
[129]
and it is false in non working days.
[132]
The way you populate this column
[133]
is totally up to you.
[136]
This version is very simple,
[137]
you might need to handle vacations
[139]
or whatever kind of holidays
[140]
you have in your country.
[141]
But the presence of the column
[143]
is extremely important.
[144]
Because the algorithm we are gonna use
[147]
is to count the number
of days in this table,
[150]
which are working days
[151]
and they happen to be in
between the two dates.
[155]
So I can create again a new column
[158]
in the sales table.
[159]
And this time it will
not be the delivery days
[163]
but the delivery working days.
[165]
So we start with a new column,
[166]
let's call it delivery working days.
[170]
Let's go with a longer but easier
[172]
to understand expression
delivery working days.
[176]
How can I do that?
[178]
Well, I needed to count the number of rows
[180]
which are in the date table.
[182]
And I will apply a filter
[183]
that's why CALCULATE becomes useful.
[185]
I do a CALCULATE, and I COUNTROWS
[188]
the number of rows in the date table.
[192]
Date table.
[194]
Then I need to start applying filters.
[196]
The first filter is that I want
[198]
to restrict the date table
[200]
to only the dates that happened
[201]
to be between the order date
[203]
and the delivery date.
[205]
So I need to use DATESBETWEEN
[209]
the date, and I want to start from
[211]
the sales order date
[214]
and the sales delivery date.
[218]
Actually, I don't want
[219]
to stop at the delivery date,
[221]
I want to stop a day earlier
[222]
because DATESBETWEEN will
include the boundaries.
[225]
So I need to use delivery date minus one.
[230]
If I stop the calculation at this point,
[232]
the result that we obtain
[234]
is exactly the same as the number of days.
[237]
It's just more complex,
[238]
but it will be nearly the same.
[240]
What I need to do is to
add the further condition.
[243]
I want that day to be a working day.
[245]
And because I have the information
[247]
in the date table,
[248]
I can say, I want also
[250]
the date ISWORKINGDAY to be true.
[254]
There is still one thing to do,
[258]
because I'm using CALCULATE
[259]
and I am in a row context,
[261]
context transition is gonna happen
[262]
and I don't want the effect
of context transition,
[265]
otherwise the filter from the order date
[267]
will be applied to the date table.
[269]
I don't want that to happen
[270]
so I will use ALL on the date
[273]
in order to get rid of
[274]
any effect of context transition,
[276]
and besides without the calculation.
[278]
If I do that the new column
[280]
will contain the number of working days.
[283]
You see that we have seven delivery dates
[286]
but out of these seven,
only five are working days.
[291]
This is now my calculated column,
[292]
if I want to produce that in a report
[294]
exactly as I did earlier,
[296]
I need to create a new measure,
[298]
let's call it average working, then...
[302]
average delivery working
days, delivery working days
[307]
which is just the AVERAGE of
[311]
the delivery working
days in the sales table.
[316]
And once I put this calculation here,
[319]
you see that the delivery working days
[321]
are always smaller than
the average working days.
[325]
It's not a complex calculation,
[327]
it's pretty simple
[328]
and it already shows you the power of DAX
[330]
when you use it in your reports.
[332]
Enjoy DAX!
Most Recent Videos:
You can go back to the homepage right here: Homepage