Forum Discussion

Analitika's avatar
Analitika
Post Prodigy
4 years ago
Solved

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

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?

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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())

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.