Forum Discussion
mellon199
4 years agoFrequent Visitor
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 formu...
- 4 years ago
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] )
Greg_Deckler
Community Champion
4 years agomellon199 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