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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends September 15. Request your voucher.

Reply
Depso
Frequent Visitor

Substract row to another row on the same column without creating new column

I have a quite interesting request.

 

I have a table like this one: 

 

TypeYearPriceValue1Value2Value3
Home20219234
Home202227567
Home202342456
Terrain20217241
Terrain202211112

 

My Price Column is calculated and gets it's value based on columns Value1, Value2 and Value3. But I need to also add the value of itself based on the previous year. 

 

Could anyone share with me how to find this solution?

 

I thought of using something like this as the calculation of Price: 

 

 var calculatedYear = 'Table'[Year]-1
 var TypeVal = [Type]
 
var Value1get=  MAXX( FILTER( 'Table','Table'[Year] = calculatedYear && 'Table'[Type]=TypeVal) , [Value1])
var Value2get=  MAXX( FILTER( 'Table','Table'[Year] = calculatedYear && 'Table'[Type]=TypeVal) , [Value2])
var Value3get=  MAXX( FILTER( 'Table','Table'[Year] = calculatedYear && 'Table'[Type]=TypeVal) , [Value3])
 
Issue is here:
var PriceVal =  MAXX( FILTER( 'Table','Table'[Year] = calculatedYear && 'Table'[Type]=TypeVal) , [Price]) 
 
var Result = Value1get + Value2get + Value3get + PriceVal
 
Return
 
Result
 
But of course, since the column does not exist yet, it cannot use the values from itself.
4 REPLIES 4
smpa01
Super User
Super User

@Depso  dos this work?

 

smpa01_0-1715701245447.png

MEASURE = 
VAR base =
    FILTER ( ALL ( tbl ), tbl[Year] <= MAX ( tbl[Year] ) )
VAR grp =
    VALUES ( tbl[Type] )
RETURN
    CALCULATE ( SUM ( tbl[Value1] ), base, grp )
        + CALCULATE ( SUM ( tbl[Value2] ), base, grp )
        + CALCULATE ( SUM ( tbl[Value3] ), base, grp )
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Depso
Frequent Visitor

Unfortunatelly, no. 

I need it to reference automatically the previous item in the same column like in the screenshot example below:

 

Depso_0-1715767678020.png

It should be based on the year.

Greg_Deckler
Community Champion
Community Champion

@Depso Maybe:

var calculatedYear = 'Table'[Year]
var previousYear = calculatedYear - 1 
var TypeVal = [Type]
 
var Value1get=  MAXX( FILTER( 'Table','Table'[Year] = calculatedYear && 'Table'[Type]=TypeVal) , [Value1])
var Value2get=  MAXX( FILTER( 'Table','Table'[Year] = calculatedYear && 'Table'[Type]=TypeVal) , [Value2])
var Value3get=  MAXX( FILTER( 'Table','Table'[Year] = calculatedYear && 'Table'[Type]=TypeVal) , [Value3])

var {YUValue1get=  MAXX( FILTER( 'Table','Table'[Year] = previousYear && 'Table'[Type]=TypeVal) , [Value1])
var PYValue2get=  MAXX( FILTER( 'Table','Table'[Year] = previousYear && 'Table'[Type]=TypeVal) , [Value2])
var PYValue3get=  MAXX( FILTER( 'Table','Table'[Year] = previousYear && 'Table'[Type]=TypeVal) , [Value3])
 
var Result = Value1get + Value2get + Value3get + PYValue1get + PYValue2get + PYValue3get
 
Return
 
Result


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

This does not work because it only takes into account the previous value and I need it to keep adding up to the original value each time

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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