topic Re: Dynamic Networkdays function with Dates from Slicer - Working Days in DAX Commands and Tips

Dynamic Networkdays function with Dates from Slicer - Working Days

PBI-Newbie — Wed, 12 Jul 2023 14:31:25 GMT

Dear all,

I am trying to have a measure that calculates the following:

Networkdays for a selected time period (chosen from the slicer "Date" from Calendar Table). I have a holiday calendar and NETWORKDAYS worked fine for the whole year, but not for the selected time period of the slicer
Calculate "working time elapsed" so the first measure divided by the networkdays of the whole month.

Thanks so much for everyones help!

Re: Dynamic Networkdays function with Dates from Slicer - Working Days

Anonymous — Fri, 14 Jul 2023 02:48:48 GMT

Hi PBI-Newbie ,

Please have a try.

Networkdays = CALCULATE( COUNTROWS('Calendar'), FILTER('Calendar', 'Calendar'[Date] >= MIN('Date'[Date]) && 'Calendar'[Date] <= MAX('Date'[Date]) && NOT('Calendar'[IsHoliday]) ) )

Working Time Elapsed = [Networkdays] / CALCULATE( COUNTROWS('Calendar'), FILTER('Calendar', YEAR('Calendar'[Date]) = YEAR(MAX('Date'[Date])) && MONTH('Calendar'[Date]) = MONTH(MAX('Date'[Date])) && NOT('Calendar'[IsHoliday]) ) )

This formula calculates the networkdays of the whole month of the latest date selected in the slicer.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Dynamic Networkdays function with Dates from Slicer - Working Days

PBI-Newbie — Fri, 14 Jul 2023 06:46:24 GMT

Thanks a lot Anonymous ! In the meantime I use three different measures to get the desired calculation.

Calendar Last Date=Calculate(Lastdate('Calendar[Date]))
Calendar First Date=Calculate(Firstdate('Calendar[Date]))
Networkdays([Calendar First Date],[Calendar Last Date],1,Values(Calendar_Holidays[Date]))

Might not be elegant, but worked out as well 🙂