Forum Discussion
Need to find difference between consecutive rows with Index sequence repeating based on criteria
Need to create a calculated column in Power Bi Named "Difference_Amount". There is an Index (sequence) column. But that restarts based on a new person's name. When the Index is 1 then "Difference_Amo...
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
But basically what you want is something like this:
Difference = VAR __Previous = MAXX(FILTER('Table',[PersonName] = EARLIER([PersonName] && [Index] = EARLIER([Index]) - 1),[Amount]) RETURN IF([Index]=1,0,[Amount] - [Previous])
Thank you so much Greg_Deckler . This works well for me. There is a minor syntax issue - After
EARLIER([PersonName]there is a closing round braces.
The expression which works for me is pasted below
Difference = Var _Previous = MAXX(
FILTER('Table',
'Table'[PersonName] = EARLIER('Table'[PersonName]) &&
'Table'[Index] = EARLIER('Table'[Index]) - 1),
'Table'[Amount])
Return If('Table'[Index]=1,0,_Previous-'Table'[Amount])
Greg_Deckler
Community Champion
Community ChampionAwesome! Glad it worked!