Forum Discussion
Create new column - shift by Year and Group
Hello!
My target is to create a new column, which shall contain the value of the previous year and group.
Any ideas?
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
2 Replies
- mwegener
Most Valuable Professional
Hi Anonymous ,
the individual rows are not unique.
Should the value for the Group per year not be unique? 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
