Calculating difference in table in Power BI

[Analitika]

Hello,

I have an issue calculating difference between values in different columns and different rows in same table. My data table example:

My query in DAX:

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

My expected result is:

But I am getting result that:

So what's wrong with my query in DAX? How to solve my issue?

0.87!=-3289.13

[v-angzheng-msft · Accepted solution]

Hi, @Analitika 

if('x'[deb] <> 0,_1 - 'x'[deb], blank())

I guess you mean DET here. 

Try this:

Column2 = 
VAR _lastDate =
    MAXX (
        FILTER (
            'Table',
            'Table'[ID] = EARLIER ( 'Table'[ID] )
        ),
        [DATE]
    )
VAR _1 =
    MAXX (
        FILTER ( 'Table', 'Table'[DATE] = _lastDate && 'Table'[ID] = EARLIER ( 'Table'[ID] ) ),
        [CRE]
    )
var _if=
    IF ( 'Table'[DET] <> 0, _1 - 'Table'[DET], BLANK () )
return _if

 Result:

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TomMartens]

Hey @Analitika ,

I assume you have to change this line

VAR _1 = MAXX(FILTER('x','x'[date] =_1 && 'x'[ID]= EARLIER('x'[ID]) ),[cre])

to this

VAR _1 = MAXX(FILTER('x','x'[date] =_0 && 'x'[ID]= EARLIER('x'[ID]) ),[cre])

Hopefully, this provides what you are looking for.

Regards,

Tom

[Analitika]

I have changed it before but still problem is not solved:

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

My expected result:

My result which is wrong:

0.87!=-3289.13