Calculate Avg. Days in Status by Priority

[PolarBear]

I've been trying to figure out a way to do this for several days, but can't get it worked out.  Hoping someone one here can help me.

I have a status table that gets updated by employees whenever the status of an order changes and a Priority table that is set by the customer (note: the customer can change the priority at any time), examples of the tables:

ID	Date	Priority
1082	1/15/2020	1
2045	1/18/2020	3
2061	1/19/2020	4
1082	1/20/2020	3
2045	1/21/2020	5

ID	Date	Status
1082	1/15/2020	Ordered
2045	1/18/2020	Ordered
2061	1/19/2020	Ordered
1082	1/18/2020	Ready for Shipment
2045	1/20/2020	Ready for Shipment
2061	1/20/2020	Ready for Shipment
1082	1/21/2020	Shipped
2045	1/21/2020	Shipped
2061	1/21/2020	Shipped

I need to find the avg. number of days spent in each status by each priority (for all orders). DateDiff works to tell me how long in each status, but I don't know how to couple that with the dates in priority. Also, I thought about creating a list for all the dates for each ID, but I have thousands of rows and believe that will be very inefficient.

Expected Results:

Avg. Days in status by priority
Priority	Ordered	Ready for Shipment
1	3
2
3	2	1
4	1	1
5

Example summary table to show how i got the results above:

ID	Priority	Date	Status	Date	Days
1082	priority 1	1/15/2020	ordered	1/15/2020	3
1082			ready for shipment	1/18/2020	2
1082	priority 3	1/20/2020			1
1082			shipped	1/21/2020	0
2045	priority 3	1/18/2020	ordered	1/18/2020	2
2045			ready for shipment	1/20/2020	1
2045	priority 5	1/21/2020	shipped		0
2061	priority 4	1/19/2020	ordered	1/19/2020	1
2061			ready for shipment	1/20/2020	1
2061			shipped	1/21/2020	0

[PolarBear · Accepted solution]

I was able to get it solved by appending the tables to get all the dates in one column, then adding calculated columns to find each change and the days between each change.

[v-diye-msft]

Hi @PolarBear 

Could you please kindly elaborate on how to get the Days in your expected table? not quite understand the calculation logic.

[PolarBear]

@v-diye-msft ,

ID 1082: Was in a prioriy 1, ordered status from 1/15/20. On 1/18/20, it changed to a prioiry 1, ready to ship status (I am calculating full days, 1/15 - 1/18 is 3 days). Then on 1/20, it changed to a priority 3, ready to ship status (1/18 - 1/20 is 2 days). Lastly, on 1/21, it shipped (effectively closing the ID)  (1/20 - 1/21 is 1 day) and  I don't want to count anything once the ID has ended (shipped).

Thank you

[amitchandak]

@PolarBear 

Create a new column

Diff = datediff([Date], maxx(filter(Table,[Id]=earlier([ID]) && [Date] <earlier([Date])),[Date]),day)

[PolarBear]

@amitchandak ,

Sorry, I'm not sure what to do with this. I tried putting it in one table and filtering the other table, but it returns all zeros.

I have the calculations to calculate the number of days in each priority (it is below), but I don't know how to use it to also correlate with the dates in the second table. 

Date Next =

CALCULATE(

MIN(Priority[Date]),

ALLEXCEPT(Priority, Priority[ID]),

Priority[Date] > EARLIER(Priority[Date] )

)

Date Diff in Days =

IF(

ISBLANK('Priority'[Date Next]),

1,

SUMX(

FILTER(DateTable,

DateTable[Date] >= 'Priority'[Date]

&& DateTable[Date] <= 'Priority'[Date Next]

),

DateTable[IsWorkDay]

) + 0)