topic Re: Need some help with methodology - calculate time in status of ordered list in Report Server

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

IbisRipley — Sat, 31 Oct 2020 02:05:07 GMT

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

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

Fowmy — Sat, 31 Oct 2020 19:54:54 GMT

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

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

IbisRipley — Mon, 02 Nov 2020 23:32:00 GMT

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.