xl2016 - median time per process step in powerpivot (datamodel) with timestamps in rows

[ryanlauder]

HI There

I have a power pivot (also in powerquery) table ProcessData that includes 3 columns:
UniqueItemRef; Status; Update (=datetimestamp)

If an item has passed through several statuses it will have several rows for those statuses (same UniqueItemRef and Status on each row)
Also, if certain updates were made while within status, the updated timestamp changes, giving also several rows
Therefore there can be several rows for each combination of UniqueItemRef-status-Update

I want to measure the duration in each status for each ticket i.e. for each UniqueItemRef-status, get the Min(Updated) VS max(updated)
Further - I then want to get the Median for the duration of any UniqueItemRef in each status. (and possibly percentile calcs in the same way)

So for example:
if 3 UniqueItemRefs A01,B01,C02 had status=InProgress for 1day, 3days, 5days respectively,
AND if the same UniqueItemRefs had then got status =Hold for 3days,4days,5days respectively…

the Median for inProgress should return 3
the Median for Hold should return 4


I am new to DAX formula so any help is appreciated thanks


sample layout

Book1
	A	B	C
1	UniqueItemRef	Status	Update (dd/mm/yyyy)
2	A01	new	01/12/2022
3	B01	inprogress	02/12/2022
4	C02	on hold	03/12/2022
5	A01	new	04/12/2022
6	B01	inprogress	05/12/2022
7	C02	on hold	06/12/2022
8	A01	inprogress	07/12/2022
9	B01	inprogress	08/12/2022
10	C02	on hold	09/12/2022
11	A01	on hold	10/12/2022
12	B01	inprogress	11/12/2022
13	C02	on hold	12/12/2022
14	A01	new	13/12/2022
15	B01	inprogress	14/12/2022
16	C02	on hold	15/12/2022
Sheet1