How to get a value year+1 in a custom column

[joelaubry]

Hello all, 

As first message in here, I hope I'm in the good section. 

In my data source file, I have some projects that can be repeted trough the years. In another column, I will have the benefits for the year mentioned. However, I would like to create a custom column showing the benefit but year +1, which is appearing in my datasource but in another rows. 

To illustrate : 

Project     year      benefit  

   001      2020     150 000

001         2021     175 0000 

I wish I could create a column showing to the right of the 150 000 the result of 175 000 as it is referenced under the same project number and other same references. 

Any idea ? 

[Anonymous · Accepted solution]

Hi @joelaubry ,

Please try to create a new column with below dax formula:

Column =
VAR cur_year = [Year]
VAR cur_project = [Project]
VAR _a =
    CALCULATE (
        MAX ( 'Table'[Benefit] ),
        FILTER ( 'Table', [Project] = cur_project && [Year] = cur_year + 1 )
    )
RETURN
    _a

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[joelaubry]

Many thanks ! I had to introduce many variables because of the filters but it works perfectly I think ! 

[Uzi2019]

Hi @joelaubry 
You can add calculated column like below
Next year= Year('Date')+1

If this is not what you want then give expected output in excel screenshot

If my post helps you give kudos and accept it as a solution!
Thanks

[joelaubry]

Hello, 

No, it's not what I meant, sorry if I explained badly. 

It's not the year +1 I want to see in the added column, it is the result of year + 1. 

Project     year      benefit    Benefit Year+1

   001      2020     150 000     175 000

001         2021     175 0000 

This is shown in my exemple just above. The idea is to return the result of 2021 but on the row of 2020. 

[Uzi2019]

Hi @joelaubry 
Please try the below measure:

Next benefits =

Var B= YEAR(DATE(Max(Next[Year]),1,1))+1

Var a=CALCULATE(SUM(Next[Benefits]),Next[Year]=B)

Return a

I hope I answered your question Please give kudos and accept it as a solution!
Thanks

[joelaubry]

I think it should work if I put all filters ! Thanks ! But I prefered the other solution. Many thanks for your help !!!!

[joelaubry]

Hi, 

sorry for late answer. I'm not sure to understand where is your "Next" function coming from. 

[Uzi2019]

Hi @joelaubry 

Next benefits =

Var B= YEAR(DATE(Max(Table[Year]),1,1))+1

Var a=CALCULATE(SUM(Table[Benefits]),Table[Year]=B)

Return a

Next is my table name.

I hope I answered yor question please give kudoa and accept it as a solution!

[Uzi2019]

Hi @joelaubry 
If there is no date column like proper date then do the following
Y2= YEAR(DATE([Y],1,1))+1

If my post helps please give kudos and accept it as a solution!
Thanks