topic Re: I need help creating calculated column that sums a value from row above it in DAX Commands and Tips

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

wrigh2uk — Wed, 27 Sep 2023 10:38:59 GMT

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

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

nirali_arora — Wed, 27 Sep 2023 10:52:51 GMT

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

)

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

wrigh2uk — Wed, 27 Sep 2023 12:40:58 GMT

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.

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

ERD — Wed, 27 Sep 2023 15:32:14 GMT

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 )

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

wrigh2uk — Tue, 21 Nov 2023 11:21:23 GMT

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