Need to find difference between consecutive rows with Index sequence repeating based on criteria

[Anonymous]

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_Amount" becomes 0.

PersonName	DateAdded	Index	Amount	Difference_Amount
Person A	2/15/2020	1	45	0
Person A	2/16/2020	2	45	0
Person A	2/17/2020	3	48	-3
Person A	2/18/2020	4	40	8
Person A	2/19/2020	5	40	0
Person A	2/20/2020	6	40	0
Person A	2/21/2020	7	40	0
Person A	2/22/2020	8	40	0
Person A	2/23/2020	9	40	0
Person A	2/24/2020	11	32	8
Person A	2/25/2020	10	40	-8
Person A	2/26/2020	12	48	-8
Person A	2/27/2020	18	40	8
Person A	2/28/2020	19	40	0
Person A	2/29/2020	20	40	0
Person A	3/1/2020	13	56	-16
Person A	3/2/2020	14	48	8
Person A	3/3/2020	15	40	8
Person A	3/4/2020	16	32	8
Person A	3/5/2020	17	48	-16
Person B	2/15/2020	1	74	0
Person B	2/16/2020	2	74	0
Person B	2/17/2020	3	74	0
Person B	2/18/2020	4	74	0
Person B	2/19/2020	5	74	0
Person B	2/20/2020	6	74	0
Person B	2/21/2020	7	74	0
Person B	2/22/2020	8	74	0
Person B	2/23/2020	9	74	0
Person B	2/24/2020	10	34	40
Person B	2/25/2020	11	34	0
Person B	2/26/2020	12	34	0
Person B	2/27/2020	14	50	-16
Person B	2/28/2020	15	50	0
Person B	2/29/2020	16	50	0
Person B	3/1/2020	13	50	0
Person B	3/2/2020	17	42	8
Person B	3/3/2020	18	34	8
Person B	3/4/2020	19	50	-16
Person B	3/5/2020	20	50	0

[Greg_Deckler · Accepted solution]

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...

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])

[Anonymous]

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]

Awesome! Glad it worked!