March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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!
@rashleysmith , 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]))
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
121 | |
98 | |
89 | |
72 | |
62 |
User | Count |
---|---|
140 | |
121 | |
106 | |
98 | |
94 |