Monthly rolling average where each month is represented in a column

[wcd1213]

Hi All,

I have a power bi table where I am trying to calculate a rolling average across 12 months. Here is how my table is set up:

ID	Year	ID Year	January	February	March	April	May	June	July	August	September	October	November	December
W-1	2022	W-1-2022	0.6	0	0.3	0.2	0	0.4	0.5	0.2	0	0	0	0.4
W-2	2022	W-2-2022	0.7	0.5	0.3	0	0.2	0.4	0.1	0	0.3	0.7	0.8	0
W-1	2023	W-1-2023	0.7	0.2	0.6	0.5	0	0.1	0.4	0.5	0	0.3	0.9	0.2
W-2	2023	W-1-2023	0.4	0.3	0.6	0.2	0	0.5	0.1	0.4	0.5	0.3	0.2	0

For examble I need W-2 from november of 2023's rolling average to have an average from November 2022 to november 2023. Is this possible with how the data is set up? 

Any help is appreciated.

[amitchandak]

@wcd1213 , Unpivot the months to be on rows, Craete a date with help from week number and then you have rolling measures like

Rolling 12 = CALCULATE(sum(Table[Value]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-12,MONTH))

You can also consider new Visual calculations

🚀 Power BI Update: Visual calculations (preview)🚀
https://powerbi.microsoft.com/en-us/blog/visual-calculations-preview/

[wcd1213]

Thank you for the reply.

The ID column is not representing weeks, how should I configure the date table then?