Forum Discussion
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.
I finally figure out how to do this (Getting an average from previous 5 years) in Power Query formula.
1. Open up Power Query Editor by clicking on "Transform Data" in PowerBI Desktop
2. In Power Query Editor, Select the table of concern
3. Select "Add Column - Custom Column" at the end of the "Applied Steps" of that table.
4. The default "New Column name" is "Custom". Change it to whatever you want. I used "Pre5yrNum"
5. In the "Custom Column formula" section, type in:
List.Average(
Table.SelectRows(
#"Source",
(C) => C[CY] < [CY] and (C[CY]>=([CY]-5))
)[Number]
)
The meaning of this is to do a List Average from a list of [Number] where CY is less than the current row CY, but more than (current CY - 5).
#"Source" is the name of the step before this step.
I think the advantage of using Power Query is that if I were to select based on more than CY, but also, Region or other fields, they can show up in a graph in Power BI.
dc7669