Forum Discussion

PBI-Newbie's avatar
PBI-Newbie
Helper I
3 years ago
Solved

Dynamic Networkdays function with Dates from Slicer - Working Days

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's avatar
    PBI-Newbie
    3 years ago

    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 🙂

2 Replies

  • PBI-Newbie's avatar
    PBI-Newbie
    Helper I
    3 years ago

    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's avatar
    Anonymous
    Not applicable
    3 years ago

    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.