Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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.
Solved! Go to Solution.
@_n_MarianLein 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/3395....
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
@_n_MarianLein 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/3395....
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
Thanks, this worked!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
26 | |
21 | |
12 | |
8 |
User | Count |
---|---|
75 | |
52 | |
45 | |
15 | |
12 |