Forum Discussion

PauloSMoura's avatar
PauloSMoura
Regular Visitor
2 years ago

Overlapping Dates times aging calculation

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 t...
Need Help
Tips and Tricks
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
    PauloSMoura
    Regular Visitor
    2 years ago

    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!