Reply
wrigh2uk
Frequent Visitor

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

Hi All,

 

 

yearactual   actual modelled
2021100   
2022200   
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

 

 

yearactual     actual modelled
2021100 
2022200 
2023     210
2024     220
2025     230
2026     240

 

really appreciate your help

1 ACCEPTED SOLUTION
ERD
Community Champion
Community Champion

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
    )

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

View solution in original post

4 REPLIES 4
ERD
Community Champion
Community Champion

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
    )

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

wrigh2uk
Frequent Visitor

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

nirali_arora
Resolver II
Resolver II

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

)

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.

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

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

Feb2025 NL Carousel

Fabric Community Update - February 2025

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

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)