How to summary data into current year and compared to previous 5-year in PowerBI Table?

Hi dc7669 

If you want to calculate the average of previous 5 years in Power Query Editor directly, you can enter below step mode in the formula bar. This will add a new column which returns the average numbers. Change previous step name according to your query. In my example, the previous step is named as "Changed Type".

= Table.AddColumn(#"Changed Type", "Average of Previous 5 Years", each List.Average(Table.SelectRows(#"Changed Type",(x)=> x[CY]>=[CY]-5 and x[CY]<[CY])[Number]))

Another method is to create a measure in the report.

Average = AVERAGEX(FILTER(ALL('Table'),'Table'[CY] >= SELECTEDVALUE('Table'[CY])-5 &&'Table'[CY] < SELECTEDVALUE('Table'[CY])),'Table'[Number])

From above screenshot, you can see that both methods get the average results. I didn't format them so the digits after decimal point are slightly different. You can format the results then. If you want users to select a year, you can add a slicer into the report and drag CY column into it for users to pick a year from.

Download the attachment for details.

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.