We have an order management table, and we’re trying to sort out a way to check how many orders we have in what status at the end of any particular day.
At our disposal, we have a table with the dates of when orders have changed status, with the names of the statuses (New, In Progress, On Hold, Complete). We’ve mocked up some sample data to try and make this clearer.
Sometimes an order will change status more than once in a day, and this is shown by an extra column called “StatusOrder”. This number should just count up, so we should be able to just use this and say to use the max status for the order, but sometimes it Just Goes Wrong and uses later number (see AAA, which has a status order by date of 1-4-3-4).
Using the example data we’ve given, we’d want to know;
At the end of 1st Jan we have 1 “New” order (BBB) and 1 “In progress” (AAA, which started as New)
At the end of 2nd Jan we have 1 “New” order (CCC), 1 “On Hold” (AAA) and 1 “Complete” (BBB)
At the end of 3rd Jan we have 1 “On Hold” order (CCC, which has gone through “New” and “In Progress”) and 2 “Complete” (AAA and BBB)
NOTE: We’re just trying to get a count of the orders in each status, so we don’t need to know wich orders are in which status - just a simple table showing “New: 1 / In Progress: 0 / On Hold: 1 / Complete: 1” when a slicer is set to 2nd January would be perfect.
Can anyone help?
