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.
Hello,
I am trying to calculate the networking days between two dates and excluding holidays and weekends.
Below is a screenshot of the data:
Case ID | Updated First Touch Date | Closed On Calendar Date | Country | HolidayVSNonHoliday | WeekEndVsWeekDay | NetWorkDays | DesiredNetworkDays |
1 | 5/8/2020 | 5/8/2020 | Italy | 1 | Weekday | 1 | 1 |
2 | 5/8/2020 | 5/18/2020 | Italy | 0 | Weekday | 10 | 6 |
3 | 5/8/2020 | 5/12/2020 | Netherlands | 1 | Weekend | 0 | 3 |
4 | 5/8/2020 | 5/15/2020 | Spain | 1 | Weekday | 8 | 6 |
5 | 5/8/2020 | 5/20/2020 | Germany | 1 | Weekday | 13 | 9 |
6 | 5/8/2020 | 5/14/2020 | Netherlands | 1 | Weekend | 0 | 5 |
Currently, with our formula we are finding the results in the column Networkdays but it is not correct, what we want in is column desired network days. We followed the logic in this video https://www.youtube.com/watch?v=dSHwQATBxgE and then we tried the following formula:
@Anonymous - There is a Quick Measure for this already in the gallery:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362#M109
@Greg_Deckler - Thanks for the reply. I applied your Dax Formula but I am still left with the challenge where some countries have the weekend on Friday and Saturday. So the solution below only works in scenarios where the weekend is Saturday and Sunday.
NetWorkDays = VAR Calendar1 = CALENDAR(MAX(NetWorkDays[created date]),MAX(NetWorkDays[review date])) VAR Calendar2 = ADDCOLUMNS(Calendar1,"WeekDay",WEEKDAY([Date],2)) RETURN COUNTX(FILTER(Calendar2,[WeekDay]<6),[Date])
Ta make it even harder I have more than 500 holidays so in your holiday formula,
NetWorkDaysHolidays =
VAR Calendar1 = CALENDAR(MAX(NetWorkDays[created date]),MAX(NetWorkDays[review date]))
//VAR Holidays = DATATABLE("Date",DATETIME,{{}})
VAR Holidays1 = DATATABLE("Date",DATETIME,
{
{"12/25/2017 12:00:00 AM"}
})
VAR Calendar2 = EXCEPT(Calendar1,Holidays1)
VAR Calendar3 = ADDCOLUMNS(Calendar2,"WeekDay",WEEKDAY([Date],2))
RETURN COUNTX(FILTER(Calendar3,[WeekDay]<6),[Date])
it is convenient if one has a few holidays but in my case with more than 500, this solution doesn't work.
I am building on the basis of these formulas, I need to figure out how to integrate the different type of weekends and the + 500 holidays.
Thanks again for your help
@Anonymous , have you already create a calendar with Workdays that exclude holiday and weekend .
Then refer to this file page2/workday diff
This is an extension of my blog in import mode: https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Difference-Across/ba-p/934397#M451
@amitchandak . Thanks for your reply. I am creating a calendar in my formula in the form of a variable. My issue is that I need to include in my formula different types of weekends, in some countries it is Saturday & Sunday. In others, it is Friday, Saturday. In addition, I need to include +500 holidays...Your solution doesn't work for me but thanks for the support
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 |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |