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
v-danhe-msft's avatar
v-danhe-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

Hi sthaya,

Based on my test, you could refer to below steps:

Sample data:

Create three measures:

Index = RANKX(ALL(Table1),FIRSTNONBLANK(Table1[Date],Table1[Date]),,ASC,Dense)
Difference = var a=[Index]-1
var b=CALCULATE(MAX('Table1'[Mileage]),FILTER(ALL('Table1'),'Table1'[Index]=a))
return MAX([Mileage])-b
Filtered difference = var a=[Index]-1
var b= CALCULATE(max('Table1'[Vehicle]),FILTER(ALL('Table1'),'Table1'[Index]=a))
return IF(b=MAX([Vehicle]),[Difference],0)

Result:

 

You could also download the pbix file to have a view:

https://www.dropbox.com/s/kb31rwaipnhr5ji/Calculate%20Miles%20Driven%20per%20Vehicle.pbix?dl=0

 

Regards,

Daniel He

 

  • 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: