Count of non-repeating days

[Matthew_BR]

Hello, I hope you are well!

I need to calculate the time a company spent with a case open in my sector, the problem is that the same company may have had several cases and some at the same time.

I calculated a simple formula for the difference between the closing and opening days of the cases, however when they are open at the same time the formula doubles the days.

I would need a formula to know only the only days that are open. For example, if there was a case opened on 01/01/2023 and closed on 01/04/2023 and another case from the same company opened on 03/01/2023 and closed on 01/05/2023, it would need to count between 01/ 01/2023 and 01/05/2023.

I tried to filter by company and get the maximum closing and minimum opening date, but in the example below it would ignore the days that were not open in June, for example.

Could someone help me, please?

[Ashish_Mathur]

Hi,

Take an example of one more Company and based on the revised table, show the expected result.  Also, share data in a format that can be pasted in an MS Excel file.

[OwenAuger]

Hi @Matthew_BR 

Assuming your 'Cases' table remains structured as-is, you need a measure that computes the date range for each row of 'Cases', takes the distinct union of these ranges, and counts the resulting set of dates.

Without relying on a 'Date' table, you could write:

Days Open = 
VAR DatesOpen =
    SELECTCOLUMNS (
        GENERATE (
            'Cases',
            CALENDAR ( 'Cases'[Case Created], 'Cases'[Case Closed] )
        ),
        "Date", [Date]
    )
VAR Result =
    COUNTROWS ( DISTINCT ( DatesOpen ) )
RETURN
    Result

With a Date table available, you could use DATESBETWEEN in place of CALENDAR with some small adjustments.

Note that I have assumed that Case Closed is included in the range. You can subtract 1 from Case Closed if it is not included.

Does this work?

[Ahmedx]

it's not so simple, I think the correct answer will be 242 but not 244

check

[OwenAuger]

Using DATEDIFF this way is equivalent to excluding Case Closed from the date range.

As I mentioned in my earlier reply, if the requirement is to exclude Case Closed from each date range, then replace  'Cases'[Case Closed] with 'Cases'[Case Closed] - 1.

Case Closed excluded: 242

Case Cosed included: 244

[Ahmedx]

in this case you need to write so that the "total" is correct