Forum Discussion

mstefancik's avatar
mstefancik
Icon for Advocate IV rankAdvocate 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.

mstefancik's avatar
mstefancik
Icon for Advocate IV rankAdvocate 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
    Icon for Community Champion rankCommunity 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.

    • mstefancik's avatar
      mstefancik
      Icon for Advocate IV rankAdvocate IV
      10 years ago

      Hi Greg_Deckler exactly, I have got Date column in my Kalendar table.

      So I should be able just copy your code. That is what I did, but got the error:

       

      Do I need to adjust something?

      • mstefancik's avatar
        mstefancik
        Icon for Advocate IV rankAdvocate IV
        10 years ago

        Already found it and now it works like a charm. Many thanks Greg_Deckler.

         

        FYI error was in the second filter, mismatch of parenthesis.

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

         

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

         

        RETURN (WDinMonth -WDinMonthThusFar)

         

         

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hello,

       

      If I have dates in the below format and  there is a condition that I can not use calendar table,

      How would I calculate networking days in a period, 

      Please see data carefully below,

       

       

       

      I have period, start date and end date and i want to calculate networking days between these 2 dates in power bi, how can I do that...

      Thanks in advance