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 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 3Responded1/9/2024
368901 4Resolved1/9/2024
3689021Created1/5/2024
3689022Activated1/9/2024
3689023Responded1/9/2024
3689024Resolved1/9/2024
3689031Created1/5/2024
3689032Activated1/9/2024
3689033Responded1/9/2024
3689091Created1/3/2024
3689092Activated1/9/2024
3689093Responded1/9/2024
3689101Created1/9/2024
3689102Activated1/9/2024
3689103Responded1/9/2024
3689104Resolved1/9/2024
3689111Created1/9/2024
3689112Activated1/9/2024
3689113Responded1/9/2024
3689121Created1/9/2024
3689122Activated1/9/2024
3689123Responded1/9/2024
3689131Created1/9/2024
3689132Activated1/9/2024
3689133Responded1/9/2024
3689134Resolved1/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)
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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    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

    • madbaird's avatar
      madbaird
      Frequent Visitor
      2 years ago

      Thank you, that seems to work as needed.

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