Event duration

[gkakun]

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, watch, open, etc (can have additional statuses in the future)

I have timestamp for each event and I need to create "Duration in days" like i created in excel in the table attached. 

Thank you all. Have merry christmas and happy new year! 

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file.  Share data of atleast 2 Escalation ID's.

[ppm1]

Please see this article that explains a great way to work with durations in DAX.

Calculate and Format Durations in DAX – Hoosier BI

Pat

[themistoklis · Accepted solution]

@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]

Its working. Thanks a lot! 

[Idrissshatila]

Hello,

You could use the datediff fucntion and specify the interval to day.

DATEDIFF(<Date1>, <Date2>, <Interval>)

like this DATEDIFF ( StartDate, EndDate, Day)

ypu can check this link for more info about datediff https://learn.microsoft.com/en-us/dax/datediff-function-dax

If I answered your question, please mark my post as solution so it would appeare to others, Appreciate your Kudo 👍

Follow me on Linkedin

[gkakun]

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