diff from previous row (group by type)

[Anonymous]

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.

[PattemManohar · Accepted solution]

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

[TomMartens]

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]

Acctually the "day" column are not consecutive 😕

There is an option to calculate the new column without using the "day" column at all ?(in some tables  i dont have this column) 

[TomMartens · Accepted solution]

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

[Anonymous]

Thanks @TomMartens and @PattemManohar !!

works great!

[PattemManohar · Accepted solution]

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