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

     

themistoklis's avatar
themistoklis
Community Champion
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

Its working. Thanks a lot!