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.
Hi all,
I have a single table of Work Orders which have a "Date Raised", "Date Removed" (i.e. Work Order rejected) and "Date Complete".
I would like to visualise the "Remaining Work Order Running Total" (i.e. "Date Raised Running Total" - "Date Removed Running Total" - "Date Closed Running Total") on a single time axis.
So far I have created the below measures for "Date Raised":
Solved! Go to Solution.
After a very frustrating night…it turns out that the issue was that my work order dates contained times as well. So, despite their format being dd/mm/yyyy, I created a new column which stripped out the times and then the measures magically started working!
I knew it would be something silly and obvious!
After a very frustrating night…it turns out that the issue was that my work order dates contained times as well. So, despite their format being dd/mm/yyyy, I created a new column which stripped out the times and then the measures magically started working!
I knew it would be something silly and obvious!
@Anonymous , based on what I got, You need to use a date table and join with all three dates with that. One active and two inactive joins. Activate the join using userelation
Hey @amitchandak , many thanks for your pointers - I have been through your HR example and tried to apply it to my scenario...please see below:
Step 1 - I have created a Date table (DimDate) using the formula below.
DimDate = CALENDAR(min('Work Orders Table'[WO_Raised]), max('Work Orders Table'[Actual Completion Date]))
Step 2 - I have linked my Raised Date ("WO_Raised"), Removed Date and Actual Completion Dates to the DimDate (I have left all the date relationships as inactive as turning them active destroyed my RAM for some reason!).
Step 3 - I created a Running Total for the Raised Work Orders.
Raised WO RT2 = CALCULATE(COUNTx(filter('Work Orders Table', 'Work Orders Table'[WO_Raised]<=max(DimDate[Date])), 'Work Orders Table'[Work Order Number]))
Step 4 - I created a running total for the Closed Work Orders.
Closed WO RT2 = CALCULATE(COUNTx(filter('Work Orders Table', 'Work Orders Table'[Actual Completion Date]>0 && 'Work Orders Table'[Actual Completion Date]<=max(DimDate[Date]) && 'Work Orders Table'[WO_Status]="Closed"), 'Work Orders Table'[Work Order Number]))
Step 5 - I visualised this to check progress.
And the cumulative total appears to work nicely...thank you!
Final query - is there a more efficient way of doing this using VAR/Result (which sounds similar to looping in Excel VBA)?
Follow-up question - any ideas why when I try to activate a relationship to simply count the number of raised work orders I get blanks when visualising against the DimDate range?
Raised WO = CALCULATE(count('Work Orders Table'[Work Order Number]), USERELATIONSHIP('Work Orders Table'[WO_Raised], DimDate[Date]))
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 |
---|---|
60 | |
58 | |
56 | |
38 | |
28 |
User | Count |
---|---|
82 | |
62 | |
45 | |
41 | |
40 |