Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hey all,
I've followed the "DATEDIFF Working Days" instructions for calculate the working days. In fact, I have a table with all the days and a "1" in the saturdays, sundays and holidays ("0" on work days). But I'm not getting what I want. Could you help me?
Also, I have a table like this:
ID | Start Date | Final Date | DATEDIFF | DATEDIFF1 |
123 | 01/01/2018 | 02/01/2018 | 1 | ? |
124 | 05/01/2018 | 10/01/2018 | 5 | ? |
125 | 15/01/2018 | 01/02/2018 | 17 | ? |
126 | 18/01/2018 | 20/01/2018 | 2 | ? |
I got a DATEDIFF between dates but I want the DATEDIFF discounts the saturdays, sundays and holidays. I thoght about calculate how many saturdays, sundays and holidays are between start and final date and then rest it in DATEDIFF column. But I don't know how I should say it to PowerBI.
Regards
Solved! Go to Solution.
See if this helps:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362
See if this helps:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362
Yes! It worked for me.
In your example you only have 1 holiday (25/12/2017). I'm trying to add more dates; 26/12/2017, etc.
Just a quick question, in order to add more holidays, you need to add them in both places (table and formula). Is it right? I thought it should be added only in the table and then the formula gets all these holidays.
Regards
No, you should just have to add them in one place. I used a DATATABLE in the measure, just so that everything was contained in the measure itself. In practice, I would expect that you would have a separate table of holidays and you would just load that into the holidays var like this:
NetWorkDaysHolidays = VAR Calendar1 = CALENDAR(MAX(NetWorkDays[created date]),MAX(NetWorkDays[review date])) VAR Holidays1 = 'HolidayTable' VAR Calendar2 = EXCEPT(Calendar1,Holidays1) VAR Calendar3 = ADDCOLUMNS(Calendar2,"WeekDay",WEEKDAY([Date],2)) RETURN COUNTX(FILTER(Calendar3,[WeekDay]<6),[Date])
In this case, you would have a table called "HolidayTable".
Hi again Greg,
I followed your instructions but Powerbi doesn't find the table. It seems I have to write a measure in "VAR Holidays1 =" but the table with all the dates are not allowed:
Regards
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
60 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |