Forum Discussion

sthaya's avatar
sthaya
Frequent Visitor
8 years ago

Calculate Miles Driven per Vehicle

Hi Power BI experts,   Below is a screenshot of a filtered Report. I would like to see how many miles driven between each gas fill-up. But I would like the formula to be by vechicle ID. In my scree...
Need Help
sthaya's avatar
sthaya
Frequent Visitor
8 years ago

Daniel,

 

thank you for your reply. I copied your measures and adjusted what I needed. Here are the 3 measures I used:

 

Index = RANKX(ALL('Gas Data'),FIRSTNONBLANK('Gas Data'[Date],'Gas Data'[Date]),,ASC,Dense)

 

 

Difference = var a=[Index]-1
var b=CALCULATE(MAX('Gas Data'[Odometer]),FILTER(ALL('Gas Data'),'Gas Data'[Index]=a))
return MAX([Odometer])-b

 

 

Miles Traveled = var a=[Index]-1
var b= CALCULATE(max('Gas Data'[Vehicle ID]),FILTER(ALL('Gas Data'),'Gas Data'[Index]=a))
return IF(b=MAX([Vehicle ID]),[Difference],0)

 

 

Here is the results when I included 2 of those measures ("Difference" and "Miles Traveled")

and here is the results when I also include the "Index" measure:

It looks like it's pulling in all the data, but using the same driver name for every row. What are your thoughts on this?

v-danhe-msft's avatar
v-danhe-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

Hi sthaya,

I noticed when you first post me the picture, the data is ordered by the date:

So, you could notice my measure with index, it ranked by the date.

When you post me the latest picture, I noticed the data you have ordered by the [Gallons Total]:

 

I have tested it, when I ordered the data by [Date] column, then create the three measures, it could work on my side:

In short, I suggest you to order your data by the [Date] column firstly, then you could try to create the three measures to test if it could work.

 

Regards,

Daniel He

 

  • sthaya's avatar
    sthaya
    Frequent Visitor
    8 years ago

    Daniel,

     

    here's what it looks like when I sort by Date:

     

    • farmerfrey's avatar
      farmerfrey
      New Member
      6 years ago

      All,

      I'm new to PowerBI and DAX, so forgive me if the previous posts solved this problem but I still need some help.

      I have a table with several different vehicles, [EquipmentID].  I need to be able to filter the table by equipment and come up with a 'Miles per Gallon' for each row.  If there is anyone who could throw me a DAX formula for this problem I would greatly appreciate it!

      Thanks