Forum Discussion

ArchStanton's avatar
ArchStanton
Power Participant
3 years ago
Solved

Calculating Working Days between multiple events

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!

 

Need Help
Show and Tell

5 Replies

  • YukiK's avatar
    YukiK
    Impactful Individual
    3 years ago

    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's avatar
      ArchStanton
      Power Participant
      3 years ago

      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!

    • ArchStanton's avatar
      ArchStanton
      Power Participant
      3 years ago

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

    • ArchStanton's avatar
      ArchStanton
      Power Participant
      3 years ago

      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