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

Get 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

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

October NL Carousel

Fabric Community Update - October 2024

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