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

[dc7669]

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

[dc7669]

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

[v-jingzhang · Accepted solution]

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.

[Syndicate_Admin]

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])

[Vera_33]

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])