Forum Discussion

hcvb07's avatar
hcvb07
Frequent Visitor
6 years ago

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

6 Replies

    • hcvb07's avatar
      hcvb07
      Frequent Visitor
      6 years ago

      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

       

    • hcvb07's avatar
      hcvb07
      Frequent Visitor
      6 years ago

      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's avatar
        amitchandak
        Super User
        6 years ago

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