Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
rashleysmith
Frequent Visitor

Running Total across multiple date columns

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!!
1 ACCEPTED SOLUTION
rashleysmith
Frequent Visitor

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!

View solution in original post

4 REPLIES 4
rashleysmith
Frequent Visitor

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
Super User
Super User

@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...

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!).

Data Model.PNG

 

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.

Visual so far.PNG

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

 

 

Blanks.PNG

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.