Help with a maintenance interval tracker - loop while function (.pbix and excel file included)

[rhaddad87]

Hi, PowerBI community!  First - thank you for all the help you've provided me since I started working with PowerBI.  I love the problems I am able to solve in my organization by using it's features.

I have a .pbix file that is used to track and report the latest status of vehicles and monitor and report maintenance intervals.  Everyday, data is collected in an excel form that reports the date, vehicle id number, number of driving hours, and any comments for that day.  Power query is also used to add an engine identifier to the vehicle as well as engines can be replaced while the vehicle ID remains the same:

The problem I have is the maintenance measures I have created and used to determine the number of driving horus and trips left until maintenance window work only when the maintenance intervals are performed according to the driving hours only.  However, sometimes staff members pull the vehicles for an early maintance window, which is tracked in the comment for that day as "200hr MX" or "400hr MX", the comment will always be the same text string ending in "XXXhr MX".  Once that is done, it throws off my maintenance tracker, as the measures have no way to know when to "restart" or start counting from zero.

I've been thinking about the solution to this for weeks - I assume it's some sort of while loop while the comment cell for the latest day does not contain the text string "hr MX?", it counts the hours and couts down until the next maintenance interval.

Sorry for the long explaination, figured more information is better than less.  Open to alternate suggestions.

Files here: https://we.tl/t-G7iHUaWnDX 

THANK YOU! 

[AlexisOlson]

What is the end result are you trying to get to? I understand the problem but it's not clear to me if you want new measures or to adjust existing measures or both. What is the main question(s) you're trying to answer with the report?

[rhaddad87]

@AlexisOlson thank you for your reply.  

The end result I am trying to achieve is a counter that keeps track of how many driving hours have occured since the last maintenance window (200 hours ago, or 400 hours ago), OR since the last time a comment box read the string "MX".  If a comment box reads the string "mx" that means that a maintenance window happened before it's scheduled time based on driving hours. That way, we are able to see how many driving hours remain until the next maintenance window.  

Hopefully that explains it clearly?

As I think more about it I think perhaps it can be achieved with conditional columns instead of a dax measure?  Im open to alternate solutions. Thank you kindly for your time

[AlexisOlson · Accepted solution]

It seems like you just need a couple of measures.

Last MX Date =
CALCULATE ( MAX ( xxAutoData[Date] ), CONTAINSSTRING ( xxAutoData[Comments], "MX" ) )

Hours since MX = 
VAR LastMX = [Last MX Date]
RETURN
    CALCULATE ( SUM ( xxAutoData[Driving Hours] ), dimDate[Date] > LastMX )

[rhaddad87]

Whoa, you just opened my eyes to a whole different solution!  It seems like I was taking the difficult approach and we don't need to do all that calcuation with the division and truncate if the comment box provides the string to rely on.  With what you provided, I am able to easily calculate any maintence window time remainig, easy plug and play.

You're a genius! THANK YOU SO MUCH! Not only for fixing my problem but showing me the better way to handle similar issues like this in the future.  Amazing!