Forum Discussion

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

Difference calculation per different rows in table in Power BI

Hello,   I would like to ask how to calculate difference between values per different rows in same table. I have this data: I expect getting result something like this. We are having additi...
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    Analitika , Please find the attached file

     

    one of the two

     

    Calc 1 =
    var _1 = maxx(FILTER('Table', [ID] =EARLIER('Table'[ID]) && [CRE]<>0) ,[CRE])
    return if([DET] <>0, _1 -[DET])


    Calc 12 =
    var _1 = maxx(FILTER('Table', [ID] =EARLIER('Table'[ID]) && [CRE]<>0 && [DATE] >EARLIER('Table'[DATE]) ) ,[CRE])
    return if([DET] <>0, _1 -[DET])

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

Analitika , Date is more in first record

 

so solution can be

 

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

 

 

or

 

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

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

amitchandak With first query I am getting result like this:

The second query also wrong. In both cases I am getting wrong answers.

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

    Analitika , Please find the attached file

     

    one of the two

     

    Calc 1 =
    var _1 = maxx(FILTER('Table', [ID] =EARLIER('Table'[ID]) && [CRE]<>0) ,[CRE])
    return if([DET] <>0, _1 -[DET])


    Calc 12 =
    var _1 = maxx(FILTER('Table', [ID] =EARLIER('Table'[ID]) && [CRE]<>0 && [DATE] >EARLIER('Table'[DATE]) ) ,[CRE])
    return if([DET] <>0, _1 -[DET])