Difference using Index

[Anonymous]

Hi, I am trying to find the difference in contractprice for same contract numbers. I have created 2 index columns and using the below dax query for calculated column but it is giving me wrong difference. 

price diff = var diff = contractlines[ContractPrice] - CALCULATE(SUM(contractlines[ContractPrice]), FILTER(contractlines, contractlines[index1] = EARLIER(contractlines[index]))) return contractlines[ContractPrice] - diff

it should give me a value of "462" for contract number 100001

[AlexisOlson · Accepted solution]

For the second row in your image, it's calculating as follows.

diff = 18,951.00 - (sum of [ContractPrice] where [index1] = 2 )
RETURN
    18,951.00 - ( 18,951.00 - 2,485,870.83 ) = 2,485,870.83

I think you want something more like this:

price diff =
VAR prev =
    LOOKUPVALUE (
        contractlines[ContractPrice],
        contractlines[ContractNumber], contractlines[ContractNumber],
        contractlines[index], contractlines[index1]
    )
RETURN
    IF ( ISBLANK ( prev ), BLANK (), contractlines[ContractPrice] - prev )