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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register 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...

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.

Top Solution Authors