How to subtract row values by previous row within a group and set the result in a new column

[user35131]

I would like to set the result in the first row as opposed to the later row. For example 

Group	Value	Index
A	10	1
A	5	2
A	3	3
B	19	1
B	15	2
C	15	1
C	10	2
C	7	3
C	17	4

Which would look like this

Group	Value	Index	Result
A	10	1	5
A	5	2	2
A	3	3	NULL
B	19	1	4
B	15	2	NULL
C	15	1	5
C	10	2	3
C	7	3	-10
C	17	4	NULL

note that many of the similar questions will send the result in the next row. I want it to stay in the first row.

[Ashish_Mathur]

Hi,

This calculated column formula works

Column = if(ISBLANK(LOOKUPVALUE(Data[Value],Data[Index],CALCULATE(MIN(Data[Index]),FILTER(Data,Data[Group]=EARLIER(Data[Group])&&Data[Index]>EARLIER(Data[Index]))),Data[Group],Data[Group])),BLANK(),Data[Value]-LOOKUPVALUE(Data[Value],Data[Index],CALCULATE(MIN(Data[Index]),FILTER(Data,Data[Group]=EARLIER(Data[Group])&&Data[Index]>EARLIER(Data[Index]))),Data[Group],Data[Group]))

Hope this helps.

[v-yiruan-msft · Accepted solution]

Hi @user35131 ,

You can create a measure as below to get it, please find the details in the attachment.

Result = 
VAR _selgroup =
    SELECTEDVALUE ( 'Table'[Group] )
VAR _selindex =
    SELECTEDVALUE ( 'Table'[Index] )
VAR _nextvalue =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Group] = _selgroup
                && 'Table'[Index] = _selindex + 1
        )
    )
RETURN
    IF ( ISBLANK ( _nextvalue ), BLANK (), SUM ( 'Table'[Value] ) - _nextvalue )

In addition, you can create a calculated column as below to get it.

Column = 
VAR _nextvalue =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Group] = EARLIER('Table'[Group])
                && 'Table'[Index] = EARLIER('Table'[Index]) + 1
        )
    )
RETURN
    IF ( ISBLANK ( _nextvalue ), BLANK (),  'Table'[Value]  - _nextvalue )

Best Regards

[amitchandak]

@user35131 , for that we need to have some date of index column

Add an index column in the power query

Index Column: https://youtu.be/NS4esnCDqVw

Then create a new column in dax like

diff = [result] - max(filter(Table, [group] = earlier([group] ) && [Index] = earlier([index]) +1 ), [result])

Power BI DAX- Earlier, I should have known Earlier: https://youtu.be/CVW6YwvHHi8

[user35131]

You have result in the formula before it is defined. Will this work? The second table is the table I want. The first table is the one I have.