Overlapping Dates times aging calculation

[PauloSMoura]

Hello Community!
I need help! I'm trying to calculate the aging between submit time and closed time of incidents by process,

But in my case, there is an overlapping scenario happening for most of the process:

P000777 Process has some incidents, highlighted rows show up the overlapping scenario.

The right calculation in this example is to consider the min non overlapping data and the last data, and then calculate the difference between those two dates.I have been trying a lot of PowerQuery solutions but my database is really heavy and its not running well those possible soluctions even with buffering tables setting.

Tks all!

PROCESS ID INCIDENT ID SUBMIT DATE              CLOSED DATE              Incorrect Aging (Days)

P0000777	86950787	09/01/2023 07:07:00	09/07/2023 17:45:00	6
P0000551	86946646	08/25/2023 15:13:00	08/28/2023 11:07:00	3
P0000777	86946248	08/24/2023 22:43:00	09/01/2023 12:08:00	8
P0000777	86944999	08/24/2023 05:35:00	09/04/2023 05:01:00	11
P0000777	86944827	08/23/2023 05:51:00	09/05/2023 02:00:00	13
P0000551	86732351	08/05/2023 09:09:00	08/15/2023 18:17:00	10
P0000777	86731911	08/03/2023 11:11:00	09/03/2023 08:12:00	31
P0000777	86731910	08/01/2023 10:15:00	08/02/2023 18:00:00	1

[v-rzhou-msft]

Hi @PauloSMoura ,

I suggest you to try code as below to create a measure.

Diff Days =
VAR _LASTDATE =
    CALCULATE (
        MAX ( 'Table'[CLOSED DATE] ),
        ALLEXCEPT ( 'Table', 'Table'[PROCESS ID] )
    )
VAR _LASTSTART =
    CALCULATE (
        MAX ( 'Table'[SUBMIT DATE] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[PROCESS ID] ),
            'Table'[CLOSED DATE] = _LASTDATE
        )
    )
VAR _LIST =
    CALENDAR ( DATEVALUE ( _LASTSTART ), DATEVALUE ( _LASTDATE ) )
VAR _MINDATE =
    CALCULATE (
        MIN ( 'Table'[SUBMIT DATE] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[PROCESS ID] ),
            DATEVALUE ( 'Table'[CLOSED DATE] ) IN _LIST
        )
    )
RETURN
    DATEDIFF ( _MINDATE, _LASTDATE, DAY )

Result is as below.

Best Regards,
Rico Zhou

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

[PauloSMoura]

Hello Tks for the Reply!
 
I've try to reply this dax formula on my database and found some problems,

The difference with this project is it is showing only 14 days and it's not capturing the whole context of the table. Also I have a question. The aging number is the date diff between the overlapping dates, but if I want to have, I like the overlapping diff and also the non-overlapping difference using the same example:

Red ones is overlapping but the first one green is not overlapping with any value, the correct aging is the sum between the overlapping range and the non overlapping.


Tks again for your reply i really appreciate that!

TKSS!

[lbendlin]

pretty much all of these are overlapping. what makes the one starting at 8/3 so special?

[PauloSMoura]

Thanks for the reply!

In this scenario, pretty much all projects have overlapping incidents, what makes the one in the example special is that it is the first one that starts the overlapping stream, so I need the submit date for this one (start of the overlapping stream) and the last date (close date) at end of the stream.

That pattern can happen in more than one time at. I mean i can have like 1~3 months of overlapping and after that an isolated value without overlapping and then start the overlapping again.