Forum Discussion

gkakun's avatar
gkakun
Helper III
3 years ago
Solved

Event duration

Hi everyone,    I tried to find solution online, but nothing I found give me the exact result I need.   I have Escalation table, with lifecycle, when its opened, moved to other status like on hold...
Need Help
  • themistoklis's avatar
    themistoklis
    3 years ago

    gkakun 

     

    Using Power Query sort the table data by Escalation ID and Date Time ASC

    Then create an Index column  in Power Query (Add Column --> Index Column)

     

    Finally create a new column with the following DAX formula:

    Duration in Days = 
  VAR Previous = MAXX(FILTER(Sheet1,Sheet1[Escalation ID]= (Sheet1[Escalation ID]) && Sheet1[Index]-1 =EARLIER(Sheet1[Index])),Sheet1[Date Time])

RETURN
  DATEDIFF(Sheet1[Date Time],Previous,HOUR) /24

     

    I have attached the PowerBI workspace

     

  • gkakun's avatar
    gkakun
    Helper III
    3 years ago

    Hi, Thanks!  Yes, I familiar with this function and tried all kind of versions with it, but nothing worked as expcted so far