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

[mpompilio]

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?

[v-stephen-msft · Accepted solution]

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.

[mpompilio]

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. 

[FGR1986]

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]))