Calculate Miles Driven per Vehicle

[sthaya]

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 screenshot below, this driver used 4 different vechicles in June. Is there a way for me to calculate in the "miles driven" column - the difference in the Mileage of each fill-up? So in row 2 of my screenshot under "Miles Driven", it should be 357,737 - 357,411. Row 3 should be 358,034 - 357,737 etc.

BUT, in row 4, since he used a different vehicle, that row should be zero or blank. Then in row 5, it should be 338,638 - 338,333.

Is what I am asking for possible?

NOTE: For the Miles Driven column, I used this formula to give me 133,277:

Miles Driven = SUMX ( VALUES ( 'Gas Data'[Card #] ), CALCULATE ( MAX ( 'Gas Data'[Mileage] ) - MIN ( 'Gas Data'[Mileage] ), NOT ISBLANK ( 'Gas Data'[Mileage] ) ) )

 

Thank you

[v-danhe-msft]

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]

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]

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]

Daniel,

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

[farmerfrey]

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