Forum Discussion

Depso's avatar
Depso
Frequent Visitor
2 years ago

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.
Need Help
Show and Tell
Tips and Tricks

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    2 years ago

    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
    • Depso's avatar
      Depso
      Frequent Visitor
      2 years ago

      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

  • smpa01's avatar
    smpa01
    Community Champion
    2 years ago

    Depso  dos this work?

     

    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 )
    • Depso's avatar
      Depso
      Frequent Visitor
      2 years ago

      Unfortunatelly, no. 

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

       

      It should be based on the year.