I need help creating calculated column that sums a value from row above it

[wrigh2uk]

Hi All,

year	actual	actual modelled
2021	100
2022	200
2023
2024
2025
2026

I'm hoping you can help me. I have this table with 3 columns.

actual modelled will be my calculated column. What I need this column to do is from 2023 add 10 on to each value. The result is that it would look something like this

year	actual	actual modelled
2021	100
2022	200
2023		210
2024		220
2025		230
2026		240

really appreciate your help

[ERD · Accepted solution]

Hi @wrigh2uk , you have 2 options. Calculated column:

actual modelled cln =
VAR tableWithValue =
    FILTER ( VALUES ( 'Table'[actual] ), 'Table'[actual] <> BLANK () )
VAR maxYearWithValue =
    CALCULATE ( MAX ( 'Table'[year] ), tableWithValue, ALL ( 'Table' ) )
VAR maxValue =
    CALCULATE (
        MAX ( 'Table'[actual] ),
        'Table'[year] = maxYearWithValue,
        ALL ( 'Table' )
    )
VAR res = maxValue + ( 'Table'[year] - maxYearWithValue ) * 10
RETURN
    IF ( 'Table'[actual] = BLANK (), res )

Measure:

actual modelled msr = 
VAR maxYearWithValue =
    CALCULATE (
        MAX ( 'Table'[year] ),
        ALL ( 'Table' ),
        'Table'[actual] <> BLANK ()
    )
VAR maxValue =
    CALCULATE (
        MAX ( 'Table'[actual] ),
        'Table'[year] = maxYearWithValue,
        ALL ( 'Table' )
    )
VAR res = maxValue + ( SELECTEDVALUE ( 'Table'[year] ) - maxYearWithValue ) * 10
RETURN
    IF (
        ISINSCOPE ( 'Table'[year] ) && SELECTEDVALUE ( 'Table'[actual] ) = BLANK (),
        res
    )

[wrigh2uk]

This was perfect for what I needed. Thank you very much

[nirali_arora]

You can use the following measure

actual modelled =

VAR BaseValue = 200 // The initial value for the year 2022

VAR YearDifference = YEAR('YourTable'[Year]) - 2022 // Calculate the difference from 2022

RETURN

IF( YearDifference > 0, // Check if it's 2023 or later

BaseValue + (YearDifference * 10), // Add 10 for each year beyond 2022

BLANK() // Leave earlier years blank

)

[wrigh2uk]

Thanks for your reply.

as a measure this doesn't work as the VAR YearDifference needs to be a aggregate function (when I try and find the field name it says cannot find otherwise). This would also go for VAR BaseValue as i can't really have this as  static value.