Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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.
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.
Hi @PolarBear
Could you please kindly elaborate on how to get the Days in your expected table? not quite understand the calculation logic.
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
Create a new column
Diff = datediff([Date], maxx(filter(Table,[Id]=earlier([ID]) && [Date] <earlier([Date])),[Date]),day)
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
99 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |