Forum Discussion
Running Total across multiple date columns
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!
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]))