Running Total across multiple date columns

[rashleysmith]

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":

   Raised WO = CALCULATE(counta('Work Orders Table'[Work Order Ref]))

   Raised WO RT = CALCULATE ([Raised WO], FILTER (ALLSELECTED('Work Orders Table'[Raised Date]),

   'Work Orders Table'[Raised Date] <= MAX ( 'Work Orders Table'[Raised Date] )))

I have the same measures for "Date Removed" and "Date Complete", using Removed Date and Completion Date respectively.

The final measure which combines the lot is:

Remaining WO RT = [Raised WO RT] - [Removed WO RT] - [Closed WO RT]

However, when I visualise this over a general date range (any of the above date ranges), I get different answers depending which range used. Also, this doesnt appear to be the running total of Work Orders remaining at the end of each time period (be it year, quarter or month).

What am I doing wrong? Any help gratefully received!!

[rashleysmith · Accepted 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!

[amitchandak]

@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

refer: https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[rashleysmith]

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)? 

[rashleysmith]

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]))