Forum Discussion

mstefancik's avatar
mstefancik
Advocate IV
10 years ago
Solved

Networkdays DAX function

Hello,

can somebody help me? Is there any simple method how to calculate Working Days left in actual month?

In excel I am using NETWORKDAYS function.

 

Any example?

 

Thanks

  • Greg_Deckler's avatar
    Greg_Deckler
    10 years ago

    Try something like this:

     

    WD = VAR WDinMonth = CALCULATE(COUNTROWS('Calendar');
FILTER('Calendar'; 'Calendar'[DayOfWeekNumber] >= 1
&& 'Calendar'[DayOfWeekNumber] <= 5 ))

VAR WDinMonthThusFar = CALCULATE(COUNTROWS('Calendar');
FILTER('Calendar'; 'Calendar'[DayOfWeekNumber] >= 1
&& 'Calendar'[DayOfWeekNumber] <= 5 ) && 'Calendar'[Date] < TODAY())

RETURN (WDinMonth -WDinMonthThusFar)

    I'm assuming you have some sort of [Date] column in your table.

7 Replies

    • mstefancik's avatar
      mstefancik
      Advocate IV
      10 years ago

      This is what I found, and tried to replicate. At this time I have got this:

       

      WD = CALCULATE(COUNTROWS('Calendar');
      FILTER('Calendar'; 'Calendar'[DayOfWeekNumber] >= 1
      && 'Calendar'[DayOfWeekNumber] <= 5 ))

       

      Which works fine. It returns count of working days in month.

      But how to filter it, to get the count of working days till the end of the month?

      So to show me for today: 5 working days, tommorow 4 working days, etc

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        10 years ago

        Try something like this:

         

        WD = VAR WDinMonth = CALCULATE(COUNTROWS('Calendar');
FILTER('Calendar'; 'Calendar'[DayOfWeekNumber] >= 1
&& 'Calendar'[DayOfWeekNumber] <= 5 ))

VAR WDinMonthThusFar = CALCULATE(COUNTROWS('Calendar');
FILTER('Calendar'; 'Calendar'[DayOfWeekNumber] >= 1
&& 'Calendar'[DayOfWeekNumber] <= 5 ) && 'Calendar'[Date] < TODAY())

RETURN (WDinMonth -WDinMonthThusFar)

        I'm assuming you have some sort of [Date] column in your table.