Forum Discussion
diff from previous row (group by type)
Hi all, need your help.
I got this table:
| type | day | num |
| A | 1 | 0 |
| A | 2 | 1 |
| A | 3 | 6 |
| A | 4 | 8 |
| A | 5 | 15 |
| A | 6 | 20 |
| B | 1 | 2 |
| B | 2 | 10 |
| B | 3 | 15 |
| B | 4 | 17 |
| B | 5 | 17 |
| B | 6 | 20 |
and want to add a column (diff) like this:
| type | day | num | diff |
| A | 1 | 0 | 0 |
| A | 2 | 1 | 1 |
| A | 3 | 6 | 5 |
| A | 4 | 8 | 3 |
| A | 5 | 15 | 7 |
| A | 6 | 20 | 5 |
| B | 1 | 2 | 0 |
| B | 2 | 10 | 8 |
| B | 3 | 15 | 5 |
| B | 4 | 17 | 2 |
| B | 5 | 17 | 0 |
| B | 6 | 20 | 3 |
The column need to calculate the difference from previous row within the group (column "type").
I tried to use EARLIER function, but didn't succeed.
Anonymous If you don't have "Day" field then add an Index field in Power Query Editor and then add a RANK for each group (which will be logically your "Day" field as mentioned above)
RankIndex = RANKX(FILTER(Test62RunningDiff,Test62RunningDiff[Type]=EARLIER(Test62RunningDiff[Type])),Test62RunningDiff[Index],,ASC)
Now you can use this "RankIndex" instead of "Day" field in the above logic for calculating "Diff" posted by TomMartens
Finally your table looks like this..
Hey Anonymous,
for your 1st example you can use this DAX statement to create the diff, the statement does not rely on consecutive days. Basically it looks for the max day that is smaller as the current day and uses this day in the LOOKUPVALUE(...) function:
diff non consecutive = var currenttype = 'Table1'[type] var currentday = 'Table1'[day] var maxPriorDay = CALCULATE( MAX('Table1'[day]) ,FILTER( ALL('Table1') ,'Table1'[type] = currenttype && 'Table1'[day] < currentday ) ) return 'Table1'[num] - LOOKUPVALUE('Table1'[num],'Table1'[type], currenttype, Table1[day], maxPriorDay)If your data does not contain something that can be used as an index (somehow you have to determine the previous row) you have to create one using PowerQuery as PattemManohar already mentioned. This can become somewhat challenging if you have to create an index for groups. Maybe this blogpost provides you with some ideas how this can be achieved using PowerQuery and some little M tweeking:
https://www.minceddata.info/2018/04/30/navigating-tabular-tables-previous-row/
Hopefully this will help you to tackle your challenges.
Regards,
Tom
5 Replies
Hey,
you can use this DAX statement to create a calculated column:
diff calc = 'Table1'[num]- LOOKUPVALUE('Table1'[num],'Table1'[type], 'Table1'[type],'Table1'[day],'Table1'[day]-1)This creates these values
Please be aware that this only works if the values in the "day" column are consecutive. If this will not be the case than it will become a little more complex.
Please tell us if the values in the day column are consecutive or not.
Regards,
Tom
Anonymous If you don't have "Day" field then add an Index field in Power Query Editor and then add a RANK for each group (which will be logically your "Day" field as mentioned above)
RankIndex = RANKX(FILTER(Test62RunningDiff,Test62RunningDiff[Type]=EARLIER(Test62RunningDiff[Type])),Test62RunningDiff[Index],,ASC)
Now you can use this "RankIndex" instead of "Day" field in the above logic for calculating "Diff" posted by TomMartens
Finally your table looks like this..