Forum Discussion

Gustavo_Rdz's avatar
Gustavo_Rdz
New Member
5 years ago
Solved

Subtract rows in a Table based on a id

Hi,   I have the followig data and I trying to subtract te row with  index 1 from the row with index 2.   TXN_START_DATE   PRODUCT   AMOUNT   INDEX   RESULT 14/03/2021   A   15 ...
Need Help
Tips and Tricks
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    Hi, Gustavo_Rdz 

    Please check the below, which is for creating a new measure.

     

     

    Result =
    VAR currentdate =
    MAX ( 'Table'[TXN_START_DATE] )
    VAR amountindextwo =
    CALCULATE (
    SUM ( 'Table'[AMOUNT] ),
    FILTER (
    ALL ( 'Table' ),
    'Table'[TXN_START_DATE] = currentdate
    && 'Table'[INDEX] = 2
    )
    )
    VAR amountindexone =
    CALCULATE (
    SUM ( 'Table'[AMOUNT] ),
    FILTER (
    ALL ( 'Table' ),
    'Table'[TXN_START_DATE] = currentdate
    && 'Table'[INDEX] = 1
    )
    )
    RETURN
    IF ( SELECTEDVALUE ( 'Table'[INDEX] ) = 2, amountindextwo - amountindexone, "" )

     

     

     

    Hi, My name is Jihwan Kim.

     

    If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

     

    Linkedin: linkedin.com/in/jihwankim1975/

    Twitter: twitter.com/Jihwan_JHKIM

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

Gustavo_Rdz , Try a new column

 

New colum =
var _max = maxx(filter(Table, [PRODUCT] =earlier([PRODUCT]) && [TXN_START_DATE] =earlier([TXN_START_DATE]) && [INDEX] <earlier([INDEX])),[INDEX])
var _max1 = maxx(filter(Table, [PRODUCT] =earlier([PRODUCT]) && [TXN_START_DATE] =earlier([TXN_START_DATE]) && [INDEX] =_max1),[AMOUNT])
return
if(isblank([_max1]), blank(), [AMOUNT] -_max1)

  • Gustavo_Rdz's avatar
    Gustavo_Rdz
    New Member
    5 years ago

    Hi Amitchandak,

     

    Thank for the quick response.

     

    I´m not ble to reproduce with the formula provided, is havingan error over _max1 for var_max1

     

    New colum = 
    var _max = maxx(filter(Table, [PRODUCT] =earlier([PRODUCT]) && [TXN_START_DATE] =earlier([TXN_START_DATE]) && [INDEX] <earlier([INDEX])),[INDEX])
    var _max1 = maxx(filter(Table, [PRODUCT] =earlier([PRODUCT]) && [TXN_START_DATE] =earlier([TXN_START_DATE]) && [INDEX] =_max1),[AMOUNT])
    return
    if(isblank([_max1]), blank(), [AMOUNT] -_max1)

    Error snapshot: