Skip to main content
cancel
Showing results for 
Search instead 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

Reply
mpompilio
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?

 

YearAmount for YearGoal For Year
FY23132432
FY22432521
FY21521674
FY20674432
FY19432none

 

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
v-stephen-msft
Community Support
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
    )
)

vstephenmsft_1-1673589044909.png

 

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.

 

View solution in original post

3 REPLIES 3
v-stephen-msft
Community Support
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
    )
)

vstephenmsft_1-1673589044909.png

 

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.

 

Hello there, 

 

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

YearAmount for YearGoal For Year
10/1/2023132432
10/1/2022432521
10/1/2021521674
10/1/2020432none
11/1/2023201521
11/1/2022521684
11/1/2021684340
11/1/2020512none



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

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors