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 .
24 Comments
kdejongeMicrosoft Employee
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.- 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
- 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
- Aside from being really useful (so should have been added by now?), why are there functions in Excel that are not in Power BI?
- I added this to the Quick Measure Gallery here: https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362
- It's not a hard issue to work around, but something that shouldn't require a work around. All current solutions rely on non-standard holiday table locations. If there was a Microsoft provided standard solution, there'd be a universal method we'd be able to use to reproduce this fairly basic functionality. There's data still living in Excel that I'm reluctant to move for this specific reason.
- I agree after LOTS os searching this post helped me to get a function which is similar. https://community.powerbi.com/t5/Desktop/Network-days-with-decimals/td-p/405476
- But it doesnot work if one of the date is blank or nulldate. is there anyway we can calculate the days between two dates column when some of the value is blank.
- The proposed method does work in calculated column. And should consider it as fundamental functions. Thanks!
- Sorry, does NOT work in calculated column
