Calculating Business Days without Calendar or Table Editing

[hcvb07]

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())

[amitchandak]

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

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.

[amitchandak]

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

[hcvb07]

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]

@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

[hcvb07]

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