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

How to calculate net working days

I need to calculate the number of days between two date fields EXCLUDING weekends. I have tried a bunch of times but can't seem to get it correct. It is a simple single data table with an "Order Date" field and "Invoice Date" field. I just want to know the number of work days between them. I know I can subtract to get total days but I need to take out the weekend days for it to be accurate for my purposes.

 

I do not want to use or import a separate Calendar Table to do this, since I have both required date fields in the original data table. Excel can do this quite easily but Power BI should have a simpler way to do it. Am I missing something?

 

i.e. An Order Date = 12/6/17 & Invoice Date = 1/9/2018 - is 34 days, but is really "24" week days.....

Capture.JPG

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: How to calculate net working days

Hi @ianneg77

 

Assuming you have  a calendar table, then you could create a calculated column with the formula provided by @robenanderson to calculated the workdays.

 

workdayflag = if(
    weekday(
       'DateTable'[date]) = 1,0,
             if(
                 weekday(
                       'DateTable'[date])=7,0,1)
)

Then you could create the networkdays column by the formula below.

 

networkdays =
CALCULATE (
    SUM ( 'DateTable'[workdayflag] ),
    DATESBETWEEN ( 'DateTable'[Date], 'Table1'[orderdate], 'Table1'[invoice date] )
)
    - 1

Capture.PNG

 

Hope this can help you!

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
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

4 REPLIES 4
robenanderson Frequent Visitor
Frequent Visitor

Re: How to calculate net working days

could you create a table (or add to a calendar table) a flag if it is a working day? for example, If M-F then 1, otherwise Sat and Sun would be 0? Then, do a calculate with the sum set to that workday flag, and a filter to the date is greater than or equal to the Order date and another filter that is less than or equal to the invoice date?

 

utilize this article for calculate with multiple filters.

ianneg77 Frequent Visitor
Frequent Visitor

Re: How to calculate net working days

I do have a calendar file I can add to. But I am not great with DAX yet been using Power BI a few months, so when it comes to filtering I may be in the weeds some.

robenanderson Frequent Visitor
Frequent Visitor

Re: How to calculate net working days

the article I linked shows how to do multiple filters, and I believe it would be something like this. I apologize in advance with my formatting. I'm not a good code formatter.

 

First, here's formula to create workday flag (1 or 0) that you can then sum in next formula. weekday = 1 is Sunday, 7 is Saturday FYI.

 

workdayflag = if(
    weekday(
         calendartable[date]) = 1,0,
             if(
                 weekday(
                       calendartable[date])=7,0,1)
)

 

then, based on the article I linked, something like this should work

 

DaysBetween MEASURE = 
CALCULATE (
    SUM ( calendar[workdayflag] ),
    FILTER ( INFOTABLE, INFOTABLE[ORDER DATE] >= calendar[DATE] && INFOTABLE[INVOICE DATE] <= calendar[DATE] )
)

 

These should at least get you close to what you're looking for. Be sure to create measure of this. You may need to create another date column or something that can have a relationship connected to it, but I could be wrong in that. Like you, I haven't been working with Power BI for more than a few months. With that said, someone more experienced may have something better to add to this, or something easier.

Community Support Team
Community Support Team

Re: How to calculate net working days

Hi @ianneg77

 

Assuming you have  a calendar table, then you could create a calculated column with the formula provided by @robenanderson to calculated the workdays.

 

workdayflag = if(
    weekday(
       'DateTable'[date]) = 1,0,
             if(
                 weekday(
                       'DateTable'[date])=7,0,1)
)

Then you could create the networkdays column by the formula below.

 

networkdays =
CALCULATE (
    SUM ( 'DateTable'[workdayflag] ),
    DATESBETWEEN ( 'DateTable'[Date], 'Table1'[orderdate], 'Table1'[invoice date] )
)
    - 1

Capture.PNG

 

Hope this can help you!

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
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

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)