Forum Discussion

Analitika's avatar
Analitika
Icon for Post Prodigy rankPost Prodigy
4 years ago
Solved

Calculating difference in table in Power BI

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

Need Help
  • v-angzheng-msft's avatar
    v-angzheng-msft
    4 years ago

    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.

3 Replies

  • TomMartens's avatar
    TomMartens
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      Analitika
      Icon for Post Prodigy rankPost Prodigy
      4 years ago

      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

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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.