Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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]))
Proud to be a 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.
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]))
Proud to be a Super User!
thanks to both 😄 !!!
You are welcome.
you are welcome
Proud to be a Super User!
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |