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

Next up in the FabCon + SQLCon recap series: The roadmap for Microsoft SQL and Maximizing Developer experiences in Fabric. All sessions are available on-demand after the live show. Register now

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
Community Champion
Community Champion

@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
New to Fabric survey Carousel

New to Fabric Survey

If you have recently started exploring Fabric, we'd love to hear how it's going. Your feedback can help with product improvements.

Power BI DataViz World Championships carousel

Power BI DataViz World Championships - June 2026

A new Power BI DataViz World Championship is coming this June! Don't miss out on submitting your entry.

March Power BI Update Carousel

Power BI Community Update - March 2026

Check out the March 2026 Power BI update to learn about new features.