Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Gustavo_Rdz
New Member

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,

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi, @Gustavo_Rdz 

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

 

Picture2.png

 

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

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


Go to My LinkedIn Page


View solution in original post

3 REPLIES 3
Jihwan_Kim
Super User
Super User

Hi, @Gustavo_Rdz 

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

 

Picture2.png

 

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

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


Go to My LinkedIn Page


amitchandak
Super User
Super User

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

 
Row-Calculation-Error.PNG

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.