How to show12 weeks rolling average in power BI?

[ehaider]

Hi All,

I need your help. 

I am getting data every week where I need to show 12 week rolling average week wise and the aggregate to 12 week rolling average in one graph. currently I am doint it on Excel but they asked me to prepare on Power BI. 

I've attach the graph that I used to make on excel. Please help me , how can i do the same on power BI.

 

[ehaider]

I need to show aggregate 12 week rooling at the last bar as shown in screenshot. 

[Chris_White]

I would advise not showing it in the same chart and instead show the overall average in a separate visual.  You could even put it right next to the chart so it looks like part of the same thing. 

Power BI is not designed to create a chart as shown.  However, if you absolutely need it as part of the same chart, there is a way to hack it in.  In outline:

1. If you haven't already, create a date table
2. Add a 'WeekName' column with data-type of text
3. In Power Query, use Table.InsertRows to add a row with a date far in the future of all other dates and a 'WeekName' of "12 Week Avg"
4. Create 2 measures.  One for your 12 weeks column, one for all other columns.
5. Create this measure, referring to the measures from step 4, which is what you use in your chart: ChartMeasure = if(SELECTEDVALUE('Date_V'[WeekName]) = "12 Week Avg",[12wkMeasure],[NormalMeasure])
6. Add a calculated column to your date table to allow you to filter your chart to the right weeks (you will otherwise find it impossible to filter your chart on date properly).  Something like this:  IsThisYear = [Year Number] = year(today()) || [WeekName] = "12 Week Avg"
7. Don't allow users to filter the chart themselves on date or they will remove the "12 Week Avg" column by accident.

Again, I would recommend not doing this as having a random extra row in your date table can easily cause knock-on problems elsewhere in your model, but the only way to have an extra column in your chart like this is to have an extra row somewhere in your data.

[Anonymous]

Hi @ehaider 

You can use the line and clustered column chart, use the column chart to display the 12 rolling average week wise, and use the line chart to display  12 week rolling average.

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.