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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

Top Solution Authors