Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
duncip
New Member

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

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!

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

You can simplify the calculations a little.

lbendlin_0-1714088124560.png

 

 

 

View solution in original post

1 REPLY 1
lbendlin
Super User
Super User

You can simplify the calculations a little.

lbendlin_0-1714088124560.png

 

 

 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors