topic Calculate duration between dates in different rows and filtered on a value in DAX Commands and Tips

Calculate duration between dates in different rows and filtered on a value

Anonymous — Wed, 02 Nov 2022 04:25:48 GMT

I have searched and searched and can't seem to find the same scenario. I am trying to calulate the duration an item takes from one step to the next. My table looks like below.

The "INC_ENTRY_ID" will appear multiple times if that specific record changes.

So, in that data woudl be something like below:

What I need to do is calculate the duration from the earliest date to the next date and then the next date, and so on for each unique "INC_ENTRY_ID" - the other columns will not be duplicated (they are either index or a different id series)

Help 🙂

I should add that I will really be drilling into this from a summary view if a user wants to see more details. So, in a different report they would be clicking on a value for the first column. So, I don't need to view everything at once... it would be a filtered view of a value from the first column.

Re: Calculate duration between dates in different rows and filtered on a value

amitchandak — Wed, 02 Nov 2022 05:00:51 GMT

Anonymous , Based on what I got, a new column

New column =

var _date = maxx(filter(Table, [INC_ENTRY_ID] = earlier([INC_ENTRY_ID]) && [Audit_date] < earlier([Audit_date]) ) , [Audit_date])

return

datediff(_date,[Audit_date], second)

Or if you need time

return

[Audit_date] - _date

Power BI DAX- Earlier, I should have known Earlier: https://youtu.be/CVW6YwvHHi8

Re: Calculate duration between dates in different rows and filtered on a value

Anonymous — Thu, 03 Nov 2022 02:15:56 GMT

amitchandak - Thank you again. I did notice one problem that I have not been able to figure out. The formula does accurately calculate the duration; however, the duration does not appear on the correct row.

So - duration is calculating correctly but it should show up on the previous row. For example, in the screenshot below, the first row started with the group "General Triage" on 10/25/2002 at 7:20 AM. It was with that group for 1980 minutes before it went to the "Virtual Desktop" group who then had it for 881 minutes and so on. Any idea how to fix that?

Re: Calculate duration between dates in different rows and filtered on a value

v-yadongf-msft — Thu, 10 Nov 2022 08:27:41 GMT

Hi Anonymous ,

Please try following DAX:

Column = var _date = MINX(FILTER('Table','Table'[INC_ENTRY_ID] = EARLIER('Table'[INC_ENTRY_ID]) && 'Table'[AUDIT_DATE] > EARLIER('Table'[AUDIT_DATE])),'Table'[AUDIT_DATE]) return DATEDIFF([AUDIT_DATE],_date,MINUTE)

The result you want:

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.