Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Victormar
Helper IV
Helper IV

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_IDDATEREADING_IDMILEAGEVALUE
11-1-21100010001000
120-1-21100120001000
127-1-2110052200200
11-2-2110103000800
23-1-21100015001500
214-1-2110032000500
22-2-221007100008000
23-3-221011130003000
220-3-221021140001000

 

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 ))
1 ACCEPTED SOLUTION
ryan_mayu
Super User
Super User

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

1.PNG





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

Proud to be a Super User!




View solution in original post

5 REPLIES 5
Ashish_Mathur
Super User
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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
ryan_mayu
Super User
Super User

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

1.PNG





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

Proud to be a Super User!




thanks to both 😄 !!!

 

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

you are welcome





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

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.