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
kyleldi
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
v-yuezhe-msft
Employee
Employee

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.

View solution in original post

17 REPLIES 17
QooT
Frequent Visitor

Maybe its old topic but easises solution is to create a custom function lik this:
(StartDate as date, EndDate as date) as number =>
let
DateList = List.Dates(StartDate, Number.FromText(EndDate-StartDate), #duration(1,0,0,0)),
RemoveWeekdns = List.Select(DateList, each Date.DayOfWeek(_, Day.Monday)<5),
CountDays = List.Count(RemoveWeekdns)
in
CountDays

And then just invoke it whenever needed.

They also just added NETWORKDAYS in DAX this month.

 

https://docs.microsoft.com/en-us/dax/networkdays-dax


Please note - I am not affiliated with Microsoft, I'm just an end user like yourself.
Just a regular guy doin' Data Science.

If my post has helped you, please don't forget to thumbs up or click "Accept as solution" if it solved your problem!

Hi,

 

I have this formula 

Days with BSCM = IF('Sheet 1'[Send form to BSCM]="No","N/A",IF(ISBLANK('Sheet 1'[BOR updated date]),"",FORMAT(NETWORKDAYS('Sheet 1'[Form Submitted on],IF(ISBLANK('Sheet 1'[BOR updated date]),TODAY(),'Sheet 1'[BOR updated date]),1),"#")))
 
Where it shows blank, I need to calculate days between Form Submitted on and Today
 
Your help will be really appreciated. 

Do I have to update my version of desktop to get this? im running 2.105

juliarusmanica
New Member

I am trying to computer network days between two dates.

 

I tried:

Days = COUNTROWS(FILTER(CALENDAR(DATE(ReceivingLog[Receipt Date].[Year],ReceivingLog[Receipt Date].[Month],ReceivingLog[Receipt Date].[Day]),date(ReceivingLog[Inspection Date].[Year],ReceivingLog[Inspection Date].[Month],ReceivingLog[Inspection Date].[Day])), WEEKDAY([Date])<>1 && WEEKDAY([Date])<>7))
 
and am getting error:
 
Cannot convert value 'January' of type Text to type Integer.
 
What can I do?
 
TY.

 

Hi,

Does this work?

Days = COUNTROWS(FILTER(CALENDAR(min(ReceivingLog[Receipt Date]),max(ReceivingLog[Inspection Date])),WEEKDAY([Date],2)<=5))


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

The issue is the Month formula requires an integer value it can't convert the text based month i.e. January.  You can try a new column that converts the months to a value something like IF(MyMonth - "Januaury", 1, IF(MyMonth = "Februaury", 2 ...  etc.

Do I add that column to my table [ReceivingLog]? or do I click Add data and create a table with one column [MonthNumber}?

 

Anonymous
Not applicable

Yes, but I just looked at your formula again and see you are taking year, month, and day from a date field.  If ReceivingLog[Receipt Date] is formated then you don't need to put it in a DATE formula i.e. COUNTAX(FILTER(ReceivingLog,ReceivingLog[Receipt Date]=>...

Anonymous
Not applicable

I found an easier solution one column and one measure, but it could be a column if you don't need it to be dynamic.

 

Column

Weekday = WEEKDAY(WEEKDAY(MyTable[MyDate],2)) Note: the 2 at the end makes it Monday 1 - Sunday 7

 

Measure

NetWorkDays = COUNTAX(FILTER(MyTable,AND(MyTable[MyDate]>= MyTable[MyStartDate], MyTable[Weekday]<6)), MyTable[MyDate]) - COUNTAX(FILTER(MyTable,AND(MyTable[MyDate]> MyTable[MyEndDate], MyTable[Weekday]<6)), MyTable[MyDate]) Note: this is counting all weekdays after the start date and then subtracting all weekdays after the end date.

 

If you needed to deal with holidays it would be pretty simple as well, just add a new column to indicate Holidays or even a seperate holiday date table and you can use that to filter out the holidays from your count.

v-yuezhe-msft
Employee
Employee

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

Hi @v-yuezhe-msft ,

 

I want Values Row by row in Calculated column followed same way as you mentioned below and it is not giving me negtive values,Please find the Below Data whichi have and Screen Captures.

Thanks In Advance.

 

Dax Formula:

TBL_Date=DATESBETWEEN('Calender Table'[Date].[Date],MasterData[Invoice Date],MasterData[Payment Date])
var TBL_FinalDate=
ADDCOLUMNS(TBL_Date,"Working day",IF(WEEKDAY([Date],2)>=6,0,1),
"Holiday",IFERROR(LOOKUPVALUE('Holiday-2019'[Holidaycount],'Holiday-2019'[Holidays - 2019,2020],[Date]),0)
)
return
SUMX(TBL_FinalDate,if([Working day]=1&&[Holiday]=0,1,0))Capture1.JPGCapture2.JPG

@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?

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))

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?

Anonymous
Not applicable

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

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.

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.