Forum Discussion

madbaird's avatar
madbaird
Frequent Visitor
2 years ago
Solved

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

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...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

amitchandak's avatar
amitchandak
Super User
2 years ago

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)))