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'm trying to do a sum to count the number of working days, excluding weekends and holidays. I've found various articles on this but struggling to get it to work correctly.
I have a calendar table where I've got:
Date
IsWorkingDay (true / false)
IsHoliday (true / false)
For example:
Then my data table has a Start Date / End Date which I've joined Start Date to my calendar in the relationship view:
I've tried to create some custom columns in my data table but it's bringing back the total number of days (i.e. 13) instead of the days that are only week days. Here's my calculation:
Workdays 1 (After excluding weekends only) =
COUNTROWS (
FILTER (
ADDCOLUMNS(
CALENDAR (ProductStages[DateCreated], ProductStages[CompletedDate (Not Blank)]),
"Is Weekday", CONTAINS('Calendar', 'Calendar'[IsWorkingDay], "TRUE")
),
[Is Weekday] = TRUE()
)
)
Here's an attempt excluding holidays, but doubt this works either:
Workdays 3 (After excluding Weekends & Holidays) =
COUNTROWS (
FILTER (
ADDCOLUMNS(
CALENDAR (ProductStages[DateCreated], ProductStages[CompletedDate (Not Blank)]),
"Is Weekday", CONTAINS('Calendar', 'Calendar'[IsWorkingDay], "True"),
"Is Holiday", CONTAINS('Calendar', 'Calendar'[IsHoliday], "False")
),
[Is Weekday] = TRUE() &&
[Is Holiday] = TRUE()
)
)
Can someone assist me, please?
Many thanks,
Dayna
Solved! Go to Solution.
@Dayna , Please use the new function networkday, it has few options to choose weekend and you can give a holiday calendar too
https://amitchandak.medium.com/power-bi-dax-function-networkdays-5c8e4aca38c
I discussed alternate in blog
@amitchandak great function and glad to see it in PowerBI, thanks so much!
Out of interest, do you know why my original calculation didn't work? Just for future reference if nothing else..
@Dayna , Please use the new function networkday, it has few options to choose weekend and you can give a holiday calendar too
https://amitchandak.medium.com/power-bi-dax-function-networkdays-5c8e4aca38c
I discussed alternate in blog
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |