cancel
Showing results for
Did you mean:
Helper II

## Showing the actual value based on a cumulative value column

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]
return
CALCULATE(
MAX('Energies ALL'[p1f2k]),
FILTER('Energies ALL',
'Energies ALL'[Vehicle_ID] = vehicles && 'Energies ALL'[Date] < dates && 'Energies ALL'[Reading_ID] = readings ))
1 ACCEPTED SOLUTION
Super User

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]))``````

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

5 REPLIES 5
Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

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]))``````

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Helper II

thanks to both 😄 !!!

Super User

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

you are welcome

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Announcements

#### The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

#### Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

#### Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors