Calculate time between different rows based on user_id and status column values

[pbimhkc]

I have a table that tracks status updates for different people. Some of the metrics I am trying to get is the duration between different statuses at the person level, and also at an aggreagate level. There are many different statuses but only a few I want to know the times between and often the statuses being compared are not in sequential rows. For example a few would be time between 'Sent' and 'Approved', 'Sent' and 'Denied', and 'Appealed' and 'Approved'. Is this something I can do with DAX given the current data structure?

person_id	status	status update
1	Sent	5/1/22 12:00
1	Approved	5/2/22 8:00
2	In Progress	5/2/22 14:15
3	In Progress	5/1/22 8:45
3	Sent	5/2/22 9:00
3	Denied	5/3/22 13:00
3	Appealed	5/4/22 8:30
3	Approved	5/5/22 15:00
4	Sent	5/4/22 10:00
4	Administrative Denial	5/5/22 9:30

For person 1 I would want to see sent to approved was 20 hours

For person 3 - sent to denied was 28 hours, appealed to approved was 30.5 hours, and sent to approved was 78 hours

For person 4 - sent to administrative denial (denial) was 23.5 hours

And then I would want averages of those - average (mean) sent to approved (20 hours and 78 hours) was 49 hours, sent to denied (28 and 23.5) was 25.75 hours, etc.

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your expected outcome of the visualization looks like, but please check the below picture and the attached pbix file.

The average of each metric is described in the Total row of the table visualization.

All measures are decribed in the attached pbix file.