Networkdays DAX function

[mstefancik]

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

[ankitpatira]

@mstefancik Check out this blog post. It may be what you want.

[mstefancik]

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 · Accepted solution]

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.

[Anonymous]

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

[mstefancik]

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]

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]

Can we get this to exclude holidays?