Create new column - shift by Year and Group

[Anonymous]

Hello!

My target is to create a new column, which shall contain the value of the previous year and group.

Any ideas?

[Anonymous · Accepted solution]

Hi  @Anonymous ,

Here are the steps you can follow：

1. Enter Power Query through Transform data, Add column -From 1.

2. Create calculated column.

rankx = RANKX(FILTER(ALL('Table'),'Table'[Year]=EARLIER('Table'[Year])),'Table'[Index],,ASC)

Average in Last Year =
CALCULATE(SUM('Table'[Average in Year]),FILTER(ALL('Table'),'Table'[Year]=EARLIER('Table'[Year])-1&&'Table'[rankx]=EARLIER('Table'[rankx])))

3. Result:

Best Regards,

Liu Yang

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

[mwegener]

Hi @Anonymous ,

the individual rows are not unique.
Should the value for the Group per year not be unique?