cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Garol Regular Visitor
Regular Visitor

Business Date Difference Between Open and Closed Using Date Table

Hello

 

I've looked through a number of threads on business datediff but a lot of them are focused on only counting non-weekend days. I haven't been able to find a thread on summing the number of business days using a date table where the business day is flag as 1 or 0. 

 

 

I have two tables in my datasource. Table1 is my main data and I'm needing to return the sum of business days from my secondary table, the Dim_Date[Business Day Flag] = 1 between Table1[OpenDate] and Table1[ClosedDate].

 

Table2 is my date table which does contain a 1 or a 0 for business day flag.  This flag is purpose built and has all public holidays and non-working days built into it's logic. 

 

Table2 is joined to the datasource and connected to Table1.[OpenDate].

 

I cannot attach a file due to sensativity. Can anyone help?

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Business Date Difference Between Open and Closed Using Date Table

Hi @Garol,

 

Please check out the demo in the attachment. One NOTE: the Cross Filter Direction should be Single.

BusinessDays =
CALCULATE (
    SUM ( Dim_Date[Business Day Flag] ),
    FILTER (
        'Dim_Date',
        'Dim_Date'[Date] >= [OpenDate]
            && 'Dim_Date'[Date] <= Table1[ClosedDate]
    )
)

Business_Date_Difference_Between_Open_and_Closed_Using_Date_Table

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 REPLIES 5
Community Support Team
Community Support Team

Re: Business Date Difference Between Open and Closed Using Date Table

Hi @Garol,

 

Please check out the demo in the attachment. One NOTE: the Cross Filter Direction should be Single.

BusinessDays =
CALCULATE (
    SUM ( Dim_Date[Business Day Flag] ),
    FILTER (
        'Dim_Date',
        'Dim_Date'[Date] >= [OpenDate]
            && 'Dim_Date'[Date] <= Table1[ClosedDate]
    )
)

Business_Date_Difference_Between_Open_and_Closed_Using_Date_Table

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Garol Regular Visitor
Regular Visitor

Re: Business Date Difference Between Open and Closed Using Date Table

Thanks for this. I think i'm close!

 

I've checked the relationship and that matches the solutions.

 

I'm getting the following error. Do i have to mix/max something here?

I don't know why i can't just copy and paste your code, given it works in your example!

Could this be a version issue with Power BI Desktop?error1.PNG

error1.PNG

Highlighted
Garol Regular Visitor
Regular Visitor

Re: Business Date Difference Between Open and Closed Using Date Table

Hi there again.

 

I went ahead and adding the min and max functions. Which will work if i can add context for a Table1[RecordID], 
How would you suggest i add in row level context for a RecordID to this calcuation?

 

BusinessDays =
CALCULATE (
    SUM(Dim_Date[BusinessDayFlag]),
    FILTER (
        'Dim_Date',
        'Dim_Date'[Date] >= MIN(Table1[OpenDate])
            && 'Dim_Date'[Date] <= MAX(Table1[CloseDate])
    )
)

Garol Regular Visitor
Regular Visitor

Re: Business Date Difference Between Open and Closed Using Date Table

Massive amatuer hour. The issue was i was using a measure...not a column. Solution is great.

Community Support Team
Community Support Team

Re: Business Date Difference Between Open and Closed Using Date Table

Hi @Garol,

 

I' m glad you solved it. The calculated column is better than a measure in this scenario.

 

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.