Subtract rows in a Table based on a id

[Gustavo_Rdz]

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		1
14/03/2021		A		33		2		18
15/03/2021		A		45		1
15/03/2021		A		62		2		17
16/03/2021		A		21		1
16/03/2021		A		43		2		22

Maybe the solution is simple, but I am quite new to this.

Regards,

[Jihwan_Kim · Accepted solution]

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]

@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]

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: