Forum Discussion
Helper ICalculate Avg. Days in Status by Priority
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 |
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.
5 Replies
- v-diye-msft
Community Support
Hi PolarBear
Could you please kindly elaborate on how to get the Days in your expected table? not quite understand the calculation logic.
PolarBearID 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
Super User
Create a new column
Diff = datediff([Date], maxx(filter(Table,[Id]=earlier([ID]) && [Date] <earlier([Date])),[Date]),day)
- PolarBear
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)
- PolarBear
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.
