Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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