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

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

Reply
hcvb07
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
amitchandak
Super User
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

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.

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

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!

parry2k
Super User
Super User

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

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490



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.

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

Start DateEnd DateDurationCount
4/13/20204/13/202003
4/13/20201/0/1900 2
4/13/20204/14/202019
4/13/20204/15/202026
4/13/20204/16/202037
4/13/20204/17/202045
4/15/20204/17/202027
4/15/20201/0/1900 0
4/13/20204/18/202049
4/15/20204/18/202026
4/13/20204/19/202048
4/15/20204/19/202029
4/13/20204/20/202052
4/15/20204/20/202038
4/20/20201/0/1900 6
4/20/20204/20/202002
4/13/20204/21/2020610
4/20/20204/21/202016

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.