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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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