Forum Discussion
Use previous row in current column calculation
Hello, trying to create a calculated column using data from the previous row.
See example below for situation which has been calculated in excel for Indexation Factor.
Indexation factor formula = B2*(1+A3)
Within excel row B1 is manually set to 1 and the fields below are using the calculation above.
Can this be replicated using a calculated column within PowerBI? Thanks in advance.
| (A) Selected Index | (B) Indexation Factor |
| 1 | |
| 0.029683 | 1.029683334 |
| 0.004578 | 1.03439749 |
| 0.049629 | 1.08573384 |
| 0.047982 | 1.137829244 |
| 0.030898 | 1.172985656 |
| 0.028848 | 1.206823699 |
| 0.019598 | 1.230475107 |
| 0.010779 | 1.24373867 |
| 0.021425 | 1.270385647 |
| 0.037423 | 1.31792673 |
| 0.030566 | 1.358210528 |
| 0.025874 | 1.393353256 |
| 0.012128 | 1.410252314 |
| 0.057762 | 1.491711364 |
| 0.123262 | 1.675582498 |
| 0.043063 | 1.747738101 |
Hi mellon199
Here is a sample file with the solution https://we.tl/t-KyxjUgcvAiFor a caclulated column:
Indexation Factor = VAR T1 = FILTER ( Data, Data[Index] <= EARLIER ( Data[Index] ) ) RETURN PRODUCTX ( T1, 1 + Data[Selected Index] )For a measure:
Indexation Factor Measure = VAR SelectedIndex = SELECTEDVALUE ( Data[Index] ) VAR T1 = CALCULATETABLE ( Data, Data[Index] <= SelectedIndex, ALL ( Data[Selected Index] ) ) RETURN PRODUCTX ( T1, 1 + Data[Selected Index] )
3 Replies
Hi mellon199
Here is a sample file with the solution https://we.tl/t-KyxjUgcvAiFor a caclulated column:
Indexation Factor = VAR T1 = FILTER ( Data, Data[Index] <= EARLIER ( Data[Index] ) ) RETURN PRODUCTX ( T1, 1 + Data[Selected Index] )For a measure:
Indexation Factor Measure = VAR SelectedIndex = SELECTEDVALUE ( Data[Index] ) VAR T1 = CALCULATETABLE ( Data, Data[Index] <= SelectedIndex, ALL ( Data[Selected Index] ) ) RETURN PRODUCTX ( T1, 1 + Data[Selected Index] )mellon199 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
