cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kyleldi Frequent Visitor
Frequent Visitor

NETWORKDAYS type function in PowerBI

I have a table (vw_WorkCenterUtilization) and a date column (Work_Date) that I need to calculate the oldest and newest date in the data, and the number of work days within it.  I'm using the FIRSTDATE and LASTDATE functions in seperate Measures to draw this number, but I do not know how to only include the number of work days in the range.  My current code to calculate the days different is as follows 

 

Days = DATEDIFF([OldestDate],[NewestDate],DAY)

 How would I go about getting this info if there is no NETWORKDAYS function like their is in Excel?  Attached is my current output in screenshot as well.  I beleive the number I'm looking for should be 261 days, not 365.

Capture4.JPG

 

 

 

 

 

Thank you!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

Re: NETWORKDAYS type function in PowerBI

Hi @kyleldi,


Firstly, create a calendar table in Power BI Desktop following the guide in this blog. Then create a column in the calendar table using the Dax below.

is work day = SWITCH(WEEKDAY([Date]),1,0,7,0,1)

 

Secondly, create a measure using the following DAX and create a card visual.

 

Days= CALCULATE(SUM('Date'[is work day]),
           DATESBETWEEN('Date'[Date], 
                        [OldestDate],
                      [NewestDate]
          ))


1.PNG

 

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 REPLIES 6
Moderator v-yuezhe-msft
Moderator

Re: NETWORKDAYS type function in PowerBI

Hi @kyleldi,


Firstly, create a calendar table in Power BI Desktop following the guide in this blog. Then create a column in the calendar table using the Dax below.

is work day = SWITCH(WEEKDAY([Date]),1,0,7,0,1)

 

Secondly, create a measure using the following DAX and create a card visual.

 

Days= CALCULATE(SUM('Date'[is work day]),
           DATESBETWEEN('Date'[Date], 
                        [OldestDate],
                      [NewestDate]
          ))


1.PNG

 

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
sureshdam18 Frequent Visitor
Frequent Visitor

Re: NETWORKDAYS type function in PowerBI

Hi,

 

I want to calculate Network days between Created Date to Till Date, My date filed looks like,

Created Date Pic.PNG 

How can we Calculate, If i am tring the above Dax calculation it is geving the error. calculation is,

is work day = SWITCH(WEEKDAY([Created Date]),1,0,7,0,1)

NetworkDays = CALCULATE(SUM([is work day]),
           DATESBETWEEN([Created Date].[Date],Sheet1[Created Date],TODAY()
          ))

Open Request = IF(AND('Fusion dashboard report'[Completion Status]="Open",  [Status]="MDM Ops Review - Undelete"),'Fusion dashboard report'[Network days], -1)

 

 While i am using this Calculation it showing the below error.

A circular dependency was detected: Fusion dashboard report[Network days], Fusion dashboard report[Open - FLS / Requestor in Days], Fusion dashboard report[Network days].

please help me on this.

 

 

Thanks in Advance.

Highlighted
Thimma Member
Member

Re: NETWORKDAYS type function in PowerBI

This looks fine, how to get number of hours as well, so times tickets open on same day and closed on same day. the mentioned formula get 1 day for this as well. but wanted to claculate hours in this scenario.

 

Thanks,
Thimma

JoMann Frequent Visitor
Frequent Visitor

Re: NETWORKDAYS type function in PowerBI

Hi,

When I try to create the measure (last step of your instructions) i get an error message: "A single value for column 'Total Revenue' in table 'SalesData' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

 

Ho wdo i resolve that? Also are we meant to create a relationship with the Date/Calendar Table and the table containing our start and end dates?

nirvana_moksh Established Member
Established Member

Re: NETWORKDAYS type function in PowerBI

@v-yuezhe-msft - hello, I tried this but it errored about because there are instances of nulls in my oldest date and newest date. Is there a way to handle that?

jcasallo Occasional Visitor
Occasional Visitor

Re: NETWORKDAYS type function in PowerBI

Replace text in red by your date fields.
 
Business_Days = COUNTROWS(FILTER(CALENDAR(DATE(2019,1,1),date(2019,12,31)), WEEKDAY([Date])<>1 && WEEKDAY([Date])<>7))