Business Date Difference Between Open and Closed Using Date Table

[Anonymous]

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?

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

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

Best Regards,
Dale

[Anonymous]

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

[Anonymous]

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

[v-jiascu-msft]

Hi @Anonymous,

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

Best Regards,
Dale

[Anonymous]

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?