Need some help with methodology - calculate time in status of ordered list

[IbisRipley]

I think this might be a simple question for many.  I have a table MyTable( status (INT) , Modified(DateTime) )  The statuses are ordered, but can be duplicated with multiple dates.

I am looking to show how long they are in each state, with the end goal to be aggrigating and charting Average time in a given status.  Can anyone point me in the right direction on methodology?  

Input table

Status     Modified

1             1/1/2020

2             1/3/2020

2              1/5/2020

3             1/6/2020

4             1/7/2020

Desired result

Status     TimeInStatus

1             2 (days)

2             3

3             1

[Fowmy · Accepted solution]

@IbisRipley 

Add this as a measure and check if it works for you:

Time In Status = 
VAR _STATUS = SELECTEDVALUE(MyTable[Status   ])
VAR _START = MIN(MyTable[ Modified])
VAR _END = MINX(FILTER(ALL(MyTable), MyTable[Status   ] > _STATUS ),MyTable[ Modified] )

RETURN
DATEDIFF( _START , _END ,DAY ) 

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[IbisRipley]

Thank you @Fowmy   I am almost there and appreciate your reply very much. 

I have used your suggestion as follows ( changed the DAY toMINUTE since test date is minimal and 

mostly created same day.

Time In Status = 
VAR _STATUS = SELECTEDVALUE(statusHistory[Status])
VAR _START = MIN(statusHistory[Modified].[Date])
VAR _END = MINX(FILTER(ALL(statusHistory), statusHistory[Status]> _STATUS ),statusHistory[Modified].[Date] )
RETURN
DATEDIFF( _START , _END ,MINUTE ) 

 Below is the output and source data.

I still have research to do but,   if I understand correctly, Do I need to add something to filter the start variable to the status grouping since all these are looking at the earliest regardless of status?

And the last thing I wondered is, if since what I want is a total of time in the status,  would I be better served doing this as a group by  in power query?

Thanks again for the help!  I will mark this answered since I was looking for guidance.