cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## How do you make a new column that equals another column's row below it?

How do I create a new column that will contain values of the other column's row below?

 Year Amount for Year Goal For Year FY23 132 432 FY22 432 521 FY21 521 674 FY20 674 432 FY19 432 none

The last column is what I am trying to make, "Goal For Year", which will take the prevous year's amount and makes it the next year's goal?

1 ACCEPTED SOLUTION
Community Support

Hi @mpompilio ,

You could create a calculated column as follows.

``````Goal For Yea =
CALCULATE (
SUM ( 'Table'[Amount for Year] ),
FILTER (
'Table',
VALUE ( RIGHT ( [Year], 2 ) )
= VALUE ( RIGHT ( EARLIER ( 'Table'[Year] ), 2 ) ) + 1
)
)
``````

Best Regards,

Stephen Tao

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

3 REPLIES 3
Community Support

Hi @mpompilio ,

You could create a calculated column as follows.

``````Goal For Yea =
CALCULATE (
SUM ( 'Table'[Amount for Year] ),
FILTER (
'Table',
VALUE ( RIGHT ( [Year], 2 ) )
= VALUE ( RIGHT ( EARLIER ( 'Table'[Year] ), 2 ) ) + 1
)
)
``````

Best Regards,

Stephen Tao

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

Frequent Visitor

Hello there,

Thank you for your response. What if I had data like this?

 Year Amount for Year Goal For Year 10/1/2023 132 432 10/1/2022 432 521 10/1/2021 521 674 10/1/2020 432 none 11/1/2023 201 521 11/1/2022 521 684 11/1/2021 684 340 11/1/2020 512 none

Is there a way to have it display the previous row this way? Thank you for your help.

Frequent Visitor

Hi, probably not the most efficient way because I'm relatively new with Power BI, but I would create two extra colunms: Fiscal Year and Previous Fiscal Year so in first row, for example, you would have 2023 / 2022 / FY23 / 132 / 432, in second 2022 / 2021 / FY22 / 432, etc. and the last column, goal for year, could be

= CALCULATE(SUM(table_name[Amount for Year]),FILTER(table_name,table_name[Fiscal Year]=EARLIER(table_name[Previous Fiscal Year]))