Dynamic Networkdays function with Dates from Slicer - Working Days

[PBI-Newbie]

Dear all, 

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

1. 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
2. Calculate "working time elapsed" so the first measure divided by the networkdays of the whole month. 

Thanks so much for everyones help!

[PBI-Newbie · Accepted solution]

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 🙂

[Anonymous]

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.