Forum Discussion
Calculate based on earlier values
Hi all,
I'm trying to translate a report that was previously in Excel to PowerBI.
In this report, there are a number of columns that are calculated based on other rows, i.e. the row above the actual record.
I've created a dummy dataset with the following raw columns:
| Date | KM total | KM trip | Fuel Price per liter | Fuel amount |
2020-06-19 | 489 | 134,2 | 1,289 | 34,91 |
| 2020-07-04 | 1081 | 299,4 | 1,209 | 27,87 |
| 2020-07-24 | 1844 | 267 | 1,239 | 32,87 |
| 2020-09-20 | 3359 | 1224,9 | 1,189 | 34,84 |
| 2020-09-27 | 3940 | 339,7 | 1,189 | 30,91 |
The columns I want to add are:
- Trip Percentage - [KM trip] / (current [KM total] - last [KM total])
- Fuel per 100KM - [Fuel amount] / (current [KM total] - last [KM total]) * 100
- Weighted KM - (current [KM total] - last [KM total]) / largest [KM total] in the whole table
- Days since last refueling - Date difference (from last [Date] to current [Date]) in days
I've marked the fixed values so that they're easier to distinguish.
I've also marked the parts I'm not clear how to execute.
If someone could point me to the correct formula, that'd be great. My main problem is: how do I address the last row in the table, i.e. from row 4, how to take a value from row 3, and so on.
Anonymous See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
The basic pattern is:
Column =
VAR __Current = [Value]
VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])
VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
__Current - __Previous
2 Replies
- Greg_Deckler
Community Champion
Anonymous See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
The basic pattern is:
Column =
VAR __Current = [Value]
VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])
VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
__Current - __Previous