Skip to main content
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

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(
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),
Super User
Super User

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

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!

Super User
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.

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/20201/0/1900 2
4/15/20201/0/1900 0
4/20/20201/0/1900 6


Helpful resources

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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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