Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mstefancik
Advocate II
Advocate II

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

1 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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

7 REPLIES 7
ankitpatira
Community Champion
Community Champion

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

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

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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,

 

 community.PNG

 

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

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:

 

Capture2.PNG

Do I need to adjust something?

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
Not applicable

Can we get this to exclude holidays?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.