Getting time between two dates in same column based on event criteria

[madbaird]

I am trying to figure out how to get the difference between two dates based on the event.
I have event IDs as follows:

1=Created

2=Activated

3=Respond

4=Resolve

5=Closed

I need to get the days between created (ID1) to either the resolve time (ID4) or if not resolved, the days open as of today's date.
Sample from Current Table:

item_id	Event_ID	Event_Name	date_hour_local
368901	1	Created	1/5/2024
368901	2	Activated	1/9/2024
368901	3	Responded	1/9/2024
368901	4	Resolved	1/9/2024
368902	1	Created	1/5/2024
368902	2	Activated	1/9/2024
368902	3	Responded	1/9/2024
368902	4	Resolved	1/9/2024
368903	1	Created	1/5/2024
368903	2	Activated	1/9/2024
368903	3	Responded	1/9/2024
368909	1	Created	1/3/2024
368909	2	Activated	1/9/2024
368909	3	Responded	1/9/2024
368910	1	Created	1/9/2024
368910	2	Activated	1/9/2024
368910	3	Responded	1/9/2024
368910	4	Resolved	1/9/2024
368911	1	Created	1/9/2024
368911	2	Activated	1/9/2024
368911	3	Responded	1/9/2024
368912	1	Created	1/9/2024
368912	2	Activated	1/9/2024
368912	3	Responded	1/9/2024
368913	1	Created	1/9/2024
368913	2	Activated	1/9/2024
368913	3	Responded	1/9/2024
368913	4	Resolved	1/9/2024

This DAX gets me close, but I just need the actual days open, so that I can use it in other parts of the design (ie Avg Days Open).

Days_Open =

VAR temp =

    TOPN(

        5,

        Filter(Ticket_Sum,

        Ticket_Sum[item_id]=EARLIER(Ticket_Sum[item_id])&&

        Ticket_Sum[date_hour_local]<EARLIER(Ticket_Sum[date_hour_local])

        ),

        [date_hour_local], desc

    )

  Return

   DATEDIFF(MINX(temp, Ticket_Sum[date_hour_local]), Ticket_Sum[date_hour_local], DAY)

[Anonymous · Accepted solution]

HI @madbaird,

You can try to use the following measure formasi lot get the difference between two event names if helps:

formula = 
VAR created =
    CALCULATE (
        MIN ( 'Table'[date_hour_local] ),
        FILTER ( ALLSELECTED ( 'Table' ), [Event_Name] = "Created" ),
        VALUES ( 'Table'[item_id] )
    )
VAR resolved =
    CALCULATE (
        MAX ( 'Table'[date_hour_local] ),
        FILTER ( ALLSELECTED ( 'Table' ), [Event_Name] = "Resolved" ),
        VALUES ( 'Table'[item_id] )
    )
VAR lastest =
    CALCULATE (
        MAX ( 'Table'[date_hour_local] ),
        ALLSELECTED ( 'Table' ),
        VALUES ( 'Table'[item_id] )
    )
VAR eventList =
    CALCULATETABLE (
        VALUES ( 'Table'[Event_Name] ),
        ALLSELECTED ( 'Table' ),
        VALUES ( 'Table'[item_id] )
    )
RETURN
    IF (
        "Resolved" IN eventList,
        DATEDIFF ( created, resolved, DAY ),
        DATEDIFF ( created, lastest, DAY )
    )

Regards,

Xiaoxin Sheng

[madbaird]

Thank you, that seems to work as needed.

[amitchandak]

@madbaird , Try new column or measure

New column =
datediff([date_hour_local],coalease( maxx(filter(Table,[item_id] = earlier([item_id]) && [Event_ID] = earlier([Event_ID])-1), [date_hour_local]),[date_hour_local]), DAY)

New Measure =
AverageX( Table, if([Event_ID] >1, datediff([date_hour_local],coalease( maxx(filter(Table,[item_id] = earlier([item_id]) && [Event_ID] = earlier([Event_ID])-1), [date_hour_local]),[date_hour_local]), DAY)))