Problem with subtracting one measure from another - both rolling distinct counts

Re: Problem with subtracting one measure from another - both rolling distinct counts

topic Re: Problem with subtracting one measure from another - both rolling distinct counts in DAX Commands and Tips

Problem with subtracting one measure from another - both rolling distinct counts

Re: Problem with subtracting one measure from another - both rolling distinct counts

duncip — Thu, 25 Apr 2024 10:41:53 GMT

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

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!

lbendlin — Thu, 25 Apr 2024 23:35:53 GMT

You can simplify the calculations a little.

CREATED_ORDERS_ROLLING

COMPLETED_ORDERS_ROLLING

OPEN_ORDERS_ROLLING