Calculate based on earlier values

[Anonymous]

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.

[Greg_Deckler · Accepted solution]

@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

[Anonymous]

Thanks, this worked!