Showing the actual value based on a cumulative value column

[Victormar]

Hi all,

I am facing a table that has values refered to mileage for several vehicles and several dates. The thing is that I would have many reading dates for each vehicle, and the mileage is cumulative. There is no relation between the dates, I mean is not exactly every day or there is any pattern related to the dates.

I would like to actually have the "real" value, meaning undoing the cumulative calculation, but I can't figure it out how to calculate it.

Thanks for your help 🙂

PD: the table would be something like

VEHICLE_ID	DATE	READING_ID	MILEAGE	VALUE
1	1-1-21	1000	1000	1000
1	20-1-21	1001	2000	1000
1	27-1-21	1005	2200	200
1	1-2-21	1010	3000	800
2	3-1-21	1000	1500	1500
2	14-1-21	1003	2000	500
2	2-2-22	1007	10000	8000
2	3-3-22	1011	13000	3000
2	20-3-22	1021	14000	1000

I came up with these formula, and I think it should work, as I have followed a tutorial, but it only shows blank values :S

Previous =

var vehicles = 'Energies ALL'[Vehicle_ID]

var dates = 'Energies ALL'[Date]

var readings = 'Energies ALL'[Reading_ID]

return

CALCULATE(

MAX('Energies ALL'[p1f2k]),

FILTER('Energies ALL',

'Energies ALL'[Vehicle_ID] = vehicles && 'Energies ALL'[Date] < dates && 'Energies ALL'[Reading_ID] = readings ))

[Ashish_Mathur]

Hi,

This calculated column formula works

Column1 = Data[MILEAGE]-LOOKUPVALUE(Data[MILEAGE],Data[DATE],CALCULATE(MAX(Data[DATE]),FILTER(Data,Data[VEHICLE_ID]=EARLIER(Data[VEHICLE_ID])&&Data[DATE]<EARLIER(Data[DATE]))),Data[VEHICLE_ID],Data[VEHICLE_ID])

Hope this helps.

[ryan_mayu · Accepted solution]

@Victormar 

pls try this

Column = 
VAR _last=maxx(FILTER('Table','Table'[VEHICLE_ID]=EARLIER('Table'[VEHICLE_ID])&&'Table'[DATE]<EARLIER('Table'[DATE])),'Table'[DATE])
return if(ISBLANK(_last),'Table'[MILEAGE],'Table'[MILEAGE]- maxx(FILTER('Table','Table'[VEHICLE_ID]=EARLIER('Table'[VEHICLE_ID])&&'Table'[DATE]=_last),'Table'[MILEAGE]))

[Victormar]

thanks to both 😄 !!!

[Ashish_Mathur]

You are welcome.

[ryan_mayu]

you are welcome