Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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!
Solved! Go to Solution.
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
16 | |
8 | |
7 | |
7 | |
6 |
User | Count |
---|---|
23 | |
11 | |
10 | |
10 | |
8 |