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_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 |
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])
3 Replies
- Greg_Deckler
Community Champion
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
Awesome! Glad it worked!