Calculating Business Days without Calendar or Table Editing

Question

Hi all!&nbsp;Fairly new to Power BI and hoping someone can help me out.&nbsp; I've been looking through some suggestions but I can't find any articles that help.&nbsp; 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]).&nbsp;I was able to calculate the calendar days between the two dates using the formula below.&nbsp; However I am struggling to create a business day counterpart.&nbsp; 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).&nbsp; I started using datesbetween with the calendar function but I keep running into errors because I need to calculate by row.&nbsp;Any suggestions?&nbsp;Calendar Days calculation:&nbsp;Duration = divide(calculate(sumx('Table2', if('Table2'[End Date]&lt;related('Table1'[Start Date]), BLANK(),DATEDIFF(related('Table1'[Start Date]),'Table2'[End Date],DAY))),'Table2'[End Date]&lt;= TODAY() &amp;&amp; 'Table2'[End Date]&lt;&gt;BLANK() &amp;&amp; 'Table2'[End Date]&lt;&gt; 0, 'Table1'[Start Date]&lt;= TODAY() &amp;&amp; 'Table1'[Start Date]&lt;&gt;BLANK()&amp;&amp;'Table1'[Start Date]&lt;&gt; 0),&nbsp;calculate(sumx('Table2', if('Table2'[End Date]&lt;related('Table1'[Start Date]),0,[Count])),'Table2'[End Date]&lt;&gt;BLANK() &amp;&amp; 'Table2'[End Date]&lt;&gt;0 &amp;&amp; 'Table2'[End Date]&lt;=TODAY(), 'Table1'[Start Date]&lt;&gt;BLANK() &amp;&amp;'Table1'[Start Date]&lt;= TODAY() &amp;&amp; 'Table1'[Start Date]&lt;&gt;0),BLANK())

parry2k · Answer

hcvb07&nbsp;better to post the sample data and expected output.&nbsp;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

amitchandak · Answer

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

hcvb07 · Answer

Ideally it would calculation for each row as below and then average to get a total duration of 2.61.Start DateEnd DateDurationCount4/13/20204/13/2020034/13/20201/0/1900&nbsp;24/13/20204/14/2020194/13/20204/15/2020264/13/20204/16/2020374/13/20204/17/2020454/15/20204/17/2020274/15/20201/0/1900&nbsp;04/13/20204/18/2020494/15/20204/18/2020264/13/20204/19/2020484/15/20204/19/2020294/13/20204/20/2020524/15/20204/20/2020384/20/20201/0/1900&nbsp;64/20/20204/20/2020024/13/20204/21/20206104/20/20204/21/202016&nbsp;

hcvb07 · Answer

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

amitchandak · Answer

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]&gt;=[Min Order Date OD] &amp;&amp; 'Date'[Date] &lt;=[Max Deilvery OD]))

Forum Discussion

Related content

Calendar Slicer with Different Business Calendars

Realtime - Business Events - cannot create business event shcema

Custom calendar

Business Requirement Document Template

Tweaking Financial Calendar