cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## Calculating Business Days without Calendar or Table Editing

Hi all!

Fairly new to Power BI and hoping someone can help me out.  I've been looking through some suggestions but I can't find any articles that help.  I'm trying to calculate Business Days (AKA exlcuding Saturdays and Sundays) between two dates in two different tables ('Table1'[Start Date] and 'Table2'[End Date]).

I was able to calculate the calendar days between the two dates using the formula below.  However I am struggling to create a business day counterpart.  My team is not the owner of the data so I cannot add a calendar table or create a calculated column to indicate a weekday (there is no calendar table in the dataset either).  I started using datesbetween with the calendar function but I keep running into errors because I need to calculate by row.

Any suggestions?

Calendar Days calculation:

Duration = divide(
calculate(
sumx('Table2',
if('Table2'[End Date]<related('Table1'[Start Date]), BLANK(),
DATEDIFF(related('Table1'[Start Date]),'Table2'[End Date],DAY))),'Table2'[End Date]<= TODAY() && 'Table2'[End Date]<>BLANK() && 'Table2'[End Date]<> 0, 'Table1'[Start Date]<= TODAY() && 'Table1'[Start Date]<>BLANK()&&'Table1'[Start Date]<> 0),
calculate(sumx('Table2', if('Table2'[End Date]<related('Table1'[Start Date]),0,[Count])),
'Table2'[End Date]<>BLANK() && 'Table2'[End Date]<>0 && 'Table2'[End Date]<=TODAY(), 'Table1'[Start Date]<>BLANK() &&'Table1'[Start Date]<= TODAY() && 'Table1'[Start Date]<>0),
BLANK())
6 REPLIES 6
Super User

@hcvb07 , refer this file I have calculated working date diff , between two date using date table

https://www.dropbox.com/s/y47ah38sr157l7t/Order_delivery_date_diff.pbix?dl=0

Frequent Visitor

Thanks for the attachment!  Based on my data, I can only use Ways 3 or 4 (I can't make calculated columns because I have a live connection).  However, both of those still result in calendar days not business days.

Super User

@hcvb07 , it is on page 2 or working day page

``Working Days = CALCULATE(Sum('Date'[Working day]),VALUES('Order Dim'[Order No]),filter(all('Date'),'Date'[Date]>=[Min Order Date OD] && 'Date'[Date] <=[Max Deilvery OD]))``
Frequent Visitor

My problem is that I don't have a calculated column that indicates 'Working Day' - I don't have a calendar table available and I can't create one since I have a live connection.  So I would need an alternative way to sum working days without creating a calculated column.  Appreciate the help and quick response!

Super User

@hcvb07 better to post the sample data and expected output. Read this post to get your answer quickly.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Frequent Visitor

Ideally it would calculation for each row as below and then average to get a total duration of 2.61.

 Start Date End Date Duration Count 4/13/2020 4/13/2020 0 3 4/13/2020 1/0/1900 2 4/13/2020 4/14/2020 1 9 4/13/2020 4/15/2020 2 6 4/13/2020 4/16/2020 3 7 4/13/2020 4/17/2020 4 5 4/15/2020 4/17/2020 2 7 4/15/2020 1/0/1900 0 4/13/2020 4/18/2020 4 9 4/15/2020 4/18/2020 2 6 4/13/2020 4/19/2020 4 8 4/15/2020 4/19/2020 2 9 4/13/2020 4/20/2020 5 2 4/15/2020 4/20/2020 3 8 4/20/2020 1/0/1900 6 4/20/2020 4/20/2020 0 2 4/13/2020 4/21/2020 6 10 4/20/2020 4/21/2020 1 6

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors