Marian Szetyinszki on 09 Apr 2021 11:19:56
Enable an engine built in formula to calculate Excel equivalent of networking days: Number of business days between 2 dates, excluding weekends and bank holidays provided as an optional list of dates.
Currently only complex solutions using other DAX / Query formulas can be used , this solution is very slow and memory intense when applied across many rows and Country holidays table in a multi country regions .
- Comments (23)
- Merged Idea (4)
RE: NETWORKDAYS DAX /Power Query formula
Yes agree I have been able to use a date calendar and holidays and sum working days but is very complex for what should be fundamental capability and simple function, still can't do it natively in SharePoint online!
https://community.powerbi.com/t5/Desktop/Number-of-working-days/td-p/22842
RE: NETWORKDAYS DAX /Power Query formula
Hi,
The proposed solution by the admin is working if we have only one data, but if we are talking about 2 dates (start date and end date) and we need measure the working days between both dates this solution will not work
RE: NETWORKDAYS DAX /Power Query formula
Hi Conrad,
We don't have this on the immediate backlog but it is pretty straightforward to do in DAX. This should do the trick if you want to filter out Saturday and Sunday:
Measure = CALCULATE(COUNTROWS('Calendar'),
FILTER('Calendar', WEEKDAY('Calendar'[Date]) > 1
&& WEEKDAY('Calendar'[Date]) < 7 ))
There are even easier ways to do this using a calculated column or Power Query where you add a column to check if a date is a working day. This will simplify the DAX above.