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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.