- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I need help creating calculated column that sums a value from row above it
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
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
This was perfect for what I needed. Thank you very much
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - February 2025
Check out the February 2025 Power BI update to learn about new features.

Subject | Author | Posted | |
---|---|---|---|
07-30-2024 12:17 PM | |||
07-31-2024 07:56 PM | |||
11-21-2023 08:06 AM | |||
02-13-2024 09:16 AM | |||
08-12-2024 10:10 PM |
User | Count |
---|---|
22 | |
12 | |
10 | |
9 | |
8 |
User | Count |
---|---|
15 | |
15 | |
15 | |
12 | |
10 |