Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
96 | |
79 | |
67 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |