Forum Discussion
Helper IIIEvent 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
- Idrissshatila
Super User
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 👍
gkakunHi, Thanks! Yes, I familiar with this function and tried all kind of versions with it, but nothing worked as expcted so far
- themistoklis
Community Champion
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
- gkakun
Its working. Thanks a lot!
ppm1Solution Sage
Please see this article that explains a great way to work with durations in DAX.
Calculate and Format Durations in DAX – Hoosier BI
Pat
- 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.
