Hi Power BI experts,
I am trying to calculate "Miles Travel" by "VEHICLE ID". I have a forumla that works - but only when I am not using any fliters.
Miles Traveled = 'Fuel Pump'[ODOMETER] - IF( 'Fuel Pump'[Index] = 0, 'Fuel Pump'[ODOMETER], LOOKUPVALUE( 'Fuel Pump'[ODOMETER], 'Fuel Pump'[Index], 'Fuel Pump'[Index]-1) )
Here is a screenshot of my Report without any filters applied. You can see the 'Miles Traveled' works fine.
But my Report will be mostly used to filter by "VEHICLE ID". My formula breaks when I filter by "VEHICLE ID". For example, I only want to see data for VEHICLE ID = 102...
I'm positive the formula breaks because the Index does not "reset" when I apply a filter. Is there a way to reset the Index when I apply a filter? Thank you!
@sthaya Cool
Perhaps:
Miles Traveled =
VAR __currentIndex = MAX('Fuel Pump'[Index])
VAR __table = ALLSELECTED('Fuel Pump')
VAR __min = MINX(__table,[Index])
VAR __previousIndex = MAXX(FILTER(__table,[Index]<__currentIndex),[Index])
VAR __currentOdometer = MAXX(FILTER(__table,[Index]=__currentIndex),[ODOMETER])
VAR __previousOdometer = MAXX(FILTER(__table,[Index]=__previousIndex),[ODOMETER])
RETURN IF(__currentIndex = __min,0,__currentOdometer - __previousOdometer)
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: The Definitive Guide to Power Query (M)
DAX is easy, CALCULATE makes DAX hard...
Helpful resources
Microsoft Fabric Learn Together
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Power BI Monthly Update - April 2024
Check out the April 2024 Power BI update to learn about new features.
Fabric Community Update - April 2024
Find out what's new and trending in the Fabric Community.
