Hey community,
I'm having trouble with what I thought would be simple. Basically I have a table that is similar to the following:
| ORDER_NO | | START_DATE | | COMPLETION_DATE |
| 1 | | 23/04/2024 | | 26/04/2024 |
| 2 | | 23/04/2024 | | NULL |
| 3 | | 24/04/2024 | | NULL |
| 4 | | 24/04/2024 | | NULL |
| 5 | | 25/04/2024 | | 26/04/2024 |
| 6 | | 26/04/2024 | | NULL |
And I've created a DAX measure to have a rolling count for both of these:
(note that DataDimension is a simple data table created in PowerBI)
CALCULATE(
DISTINCTCOUNT(DATASET[ORDER_NUMBER]),
Filter(All(DateDimension[TheDate]),DateDimension[TheDate]<=Max(DateDimension[TheDate]))
)
This will, by day, keep a rolling count how many orders have been created. So if I already had 3 orders created on 25/04/2024, because 2 more came in, the result would be 5. I also have a separate one for completed orders:
CALCULATE(
DISTINCTCOUNT(DATASET[ORDER_NUMBER]),
Filter(All(Completed_DateDimension[TheDate]),Completed_DateDimension[TheDate]<=Max(Completed_DateDimension[TheDate]))
)
It's exactly the same except uses the Completed_DateDimension table which is linked to the COMPLETION_DATE column. What I now want is to subtract the completed orders from the started so what I want to end up with is a table that looks like this
| DATE | | CREATED_ORDERS_ROLLING | | COMPLETED_ORDERS_ROLLING | | OPEN_ORDERS_ROLLING |
| 23/04/2024 | | 2 | | 0 | | 2 |
| 24/04/2024 | | 4 | | 0 | | 4 |
| 25/04/2024 | | 5 | | 0 | | 5 |
| 26/04/2024 | | 6 | | 2 | | 4 |
I would use SQL for this and make it very easy but the organisation's data is messed up and the tables can't be stored in SQL at the moment. Would really appreciate any help!
