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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. 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
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
Super User
Super User

@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!:
Power BI Cookbook Third Edition (Color)

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
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

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

June 2025 community update carousel

Fabric Community Update - June 2025

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