Calculating Working Days between multiple events

[ArchStanton]

Hi,

I would like to calculate the Working Days only for a Case between Received and Resolved dates. This is easy if it was just that simple because occasionally a Case can be put on hold (deferred) for several days or weeks, and in the example I have provided, ID 4 was deferred twice = 

Deferred Start    Deferred End Date

10/10/2022       - 24/10/2022

14/11/2022       - 29/12/2022

I would like to exclude any deferral Days from the overall Working Day calculation.

My date table has 2 columns to say whether a date is a Working Day, 1 = True & 0 = False, and a Binary TRUE/FALSE Column.

When I tried doing this I kept getting error messages referring to both of these columns.

Can someone write a measure that calculates the Total Case Length in working days only for all 4 Test IDs please? Please exclude the Deferral days from the measure.

I have been struggling with the deferral part for months.

I have uploaded a pbix file here:

https://ufile.io/g2wuldxo

Any help would be greatly appreciated.

Thanks!

[CNENFRNL · Accepted solution]

[ArchStanton]

Hi,

Quick question, there have been a few occurences where there are even more Deferred Start & End Dates, I have one with 4 different dates so 4 rows in the deferral table. Is it possible to modify this code to COUNTROWS of each ID to factor in these multiple scenarios?

The code works just fine with 2 row as per example you have provided, its just falls down when there are more than 2.

Thanks

[ArchStanton]

This is exactly what I needed, thank you so much!!!

[YukiK]

You can get working days like this:

CALCULATE(
    COUNTROWS ( 'Date2' ),
    DATESBETWEEN ( 'Date2'[Date],  'Cases'[Date Received], 'Cases'[Date Resolved] ),
    'Date2'[Is Working Day?] = TRUE,
    ALL ( Date2 )
)

[ArchStanton]

This isn't what I was after as I already have this code. The other respondent in this thread has nailed it so all good now.

Thanks for your input anyway!