Forum Discussion
Resolver IIHow to summary data into current year and compared to previous 5-year in PowerBI Table?
Hi,
I have a table like this:
| CY | Number |
| 2021 | 2768 |
| 2020 | 3355 |
| 2019 | 3762 |
| 2018 | 2857 |
| 2017 | 3521 |
| 2016 | 2711 |
| 2015 | 3323 |
| 2014 | 3877 |
| 2013 | 3546 |
| 2012 | 3891 |
| 2011 | 5227 |
I would like to report the above as a PowerBI Report. I would like the user to be able to choose the Year to compare with and show the average of the previous 5 years.
| CY | Number |
| 2021 | 2726 |
| 2016-2020 | 3241 (Averages of 2016-2020) |
Any idea on how I can achieve this?
One idea I got is to "Transform" the table into this format... but, then, how can I put the "correct" number in each cell using Power Query?
| CY | Number | CY-1 | CY-2 | CY-3 | CY-4 | CY-5 |
| 2021 | 2768 | |||||
| 2020 | 3355 | |||||
| 2019 | 3762 | |||||
| 2018 | 2857 |
Any suggestions or pointers?
Thanks!
dchan
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.
4 Replies
v-jingzhangCommunity Support
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.
dc7669I 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
Hi dc7669
You want users to choose the Year to compare, so it is a slicer, it is a more DAX question to me. If you can use this format, Last 5 Year and AVG are all measures - I did in Excel, you can adjust in Power BI
Last 5 Year:= VAR CurYear =IF(HASONEVALUE(Table1[CY]),VALUES(Table1[CY]),MAX(Table1[CY])) VAR MinYear =MINX(ALL(Table1[CY]),Table1[CY]) VAR StartYear = IF(CurYear-5>MinYear,CurYear-5,MinYear) RETURN IF(CurYear=MinYear,CurYear,StartYear&"-"&CurYear-1) AVG_NUMBER:= VAR CurYear =IF(HASONEVALUE(Table1[CY]),VALUES(Table1[CY]),MAX(Table1[CY])) VAR MinYear =MINX(ALL(Table1[CY]),Table1[CY]) VAR StartYear = IF(CurYear-5>MinYear,CurYear-5,MinYear) RETURN AVERAGEX(FILTER(ALL(Table1),Table1[CY]>=StartYear&&Table1[CY]<CurYear),Table1[Number])- Syndicate_Admin
Administrator
Hi @dc7669
You want users to choose the Year to compare, so it is a slicer, it is a more DAX question to me. If you can use this format, Last 5 Year and AVG are all measures - I did in Excel, you can adjust in Power BI
Last 5 Year:= VAR CurYear =IF(HASONEVALUE(Table1[CY]),VALUES(Table1[CY]),MAX(Table1[CY])) VAR MinYear =MINX(ALL(Table1[CY]),Table1[CY]) VAR StartYear = IF(CurYear-5>MinYear,CurYear-5,MinYear) RETURN IF(CurYear=MinYear,CurYear,StartYear&"-"&CurYear-1) AVG_NUMBER:= VAR CurYear =IF(HASONEVALUE(Table1[CY]),VALUES(Table1[CY]),MAX(Table1[CY])) VAR MinYear =MINX(ALL(Table1[CY]),Table1[CY]) VAR StartYear = IF(CurYear-5>MinYear,CurYear-5,MinYear) RETURN AVERAGEX(FILTER(ALL(Table1),Table1[CY]>=StartYear&&Table1[CY]<CurYear),Table1[Number])
