Calculating difference of values per different rows in same table in Power BI

[Analitika]

Hello,

I am trying to calculate difference between values but getting wron answer.

Here is my data table as example.

I have tried to use this formula:

Difference =
VAR _0 = MAXX(FILTER('x','x'[date]<EARLIER('x'[date]) && 'x'[ID]<EARLIER('x'[ID])),[date])
VAR _1 = MAXX(FILTER('x','x'[date] =_1 && 'x'[ID]<EARLIER('x'[ID]) ),[cre])
return
if('x'[deb] <> 0,_1 - 'x'[deb], blank())

My expected result is:

But I am getting wrong result as:

Seems that 0.87!=-3289.13.

So what's wrong with my query?

[Anonymous · Accepted solution]

Hi @Analitika 

Try this code.

Difference = 
VAR _MaxDate =
    MAXX (
        FILTER (
            'x',
            'x'[date] > EARLIER ( 'x'[date] )
                && 'x'[ID] = EARLIER ( 'x'[ID] )
        ),
        [date]
    )
VAR _CRE =
    SUMX (
        FILTER ( ALL ( 'x' ), 'x'[date] = _MaxDate && 'x'[ID] = EARLIER ( 'x'[ID] ) ),
        [CRE]
    )
RETURN
    IF ( x[DET] <> 0, _CRE - x[DET], BLANK () )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Analitika , small change

Column =
VAR _0 = MAXX(FILTER('x','x'[date]<EARLIER('x'[date]) && 'x'[ID]= EARLIER('x'[ID])),[date])
VAR _1 = MAXX(FILTER('x','x'[date] =_1 && 'x'[ID]= EARLIER('x'[ID]) ),[cre])
return
if('x'[deb] <> 0,_1 - 'x'[deb], blank())