Conrad Wong on 24 Apr 2015 01:40:13
- Comments (7)
RE: Please add the networkdays function in Excel to DAX
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.
RE: Please add the networkdays function in Excel to DAX
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.
RE: Please add the networkdays function in Excel to DAX
I added this to the Quick Measure Gallery here:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362
RE: Please add the networkdays function in Excel to DAX
Aside from being really useful (so should have been added by now?), why are there functions in Excel that are not in Power BI?
RE: Please add the networkdays function in Excel to DAX
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: Please add the networkdays function in Excel to DAX
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: Please add the networkdays function in Excel to DAX
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.