Forum Discussion
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, 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!
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) /24I have attached the PowerBI workspace
6 Replies
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 👍
Hi, Thanks! Yes, I familiar with this function and tried all kind of versions with it, but nothing worked as expcted so far
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) /24I have attached the PowerBI workspace
Its working. Thanks a lot!
Please see this article that explains a great way to work with durations in DAX.
Calculate and Format Durations in DAX – Hoosier BI
Pat
Hi,
Share data in a format that can be pasted in an MS Excel file. Share data of atleast 2 Escalation ID's.
