Forum Discussion
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 | 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,
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
3 Replies
- Jihwan_Kim
Super User
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)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:
