Forum Discussion

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

Substraction calculation between values in Power Bi

Hello,

 

I would like to ask how calculate difference between values which are in different rows and columns.

My query is: 

 

Column =
VAR _0 = MINX(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 data sample is:

My output is :

Expected result is:

Seems results don't match because in difference column values are different (0.87!-3289.13)

So how to solve this issue?

Need Help
  • v-yingjl's avatar
    v-yingjl
    4 years ago

    Hi Analitika ,

    Based on your formula and your expected output, you could change '>' to '=' in the formula like this:

    Difference = 
VAR _0 =
    MINX (
        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'[DET] <> 0, _1 - 'x'[DET], BLANK () )

     

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

2 Replies

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

    Hi Analitika 

     

    Understand you wnat to calculate the difference between CRE and DET when the DET value is not Zero.

    For this you can add a column with this code:

     

    Column =
if([DET] <> 0,[CRE] - [DET], blank())

     

     

    If I got your point in the wrong way, please add more details with a sample of your data.

     

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

    Appreciate your Kudos!!

     

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

    Hi Analitika ,

    Based on your formula and your expected output, you could change '>' to '=' in the formula like this:

    Difference = 
VAR _0 =
    MINX (
        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'[DET] <> 0, _1 - 'x'[DET], BLANK () )

     

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