Subtracting the value of the odometer column based on the latest and oldest dates.

[junaaaa]

I have odometer values for various device names. I want to calculate the odometer value of the latest date minus the odometer value of the oldest date. How to calculate it with PowerBI?

[dk_dk · Accepted solution]

Since the odometer value is increasing over time, it will be smallest at the oldest date and largest at the latest date, so you could just create a measure like this:

measure = MAX(Table[Value.data.odometer])-MIN(Table[Value.data.odometer]) and it should get you the correct calculation.

In case there is a scenario where the minimum and maximum values are not on the oldest and latest dates, you could use CALCULATE.

Something like this:

measure =
VAR earliest_value = CALCULATE(MIN(Table[Value.data.odometer]),Table[Value.data.received_at]=MIN(Table[Value.data.received_at]))

VAR latest_value= CALCULATE(MIN(Table[Value.data.odometer]),Table[Value.data.received_at]=MAX(Table[Value.data.received_at]))

RETURN
latest_value - earliest_value