Per Product 12-months' Rolling Average

[EBGAL]

Hi, 

I want to create a 12-months rolling average as a column in Power BI. I need it as a column as will need to then merge it to a different table.

If there are less than 12 months of data prior to the date then average whatever data is available.

Data is given on a monthly scale. 

Example, if I have this data:

Product	AnalysisMonth	Sales
A	Mar-14	14
A	May-14	11
A	Jun-14	12
A	May-15	21
A	Jun-15	19
B	Apr-14	11
B	May-15	15
B	Jun-15	6

I want to get this view

Product	AnalysisMonth	12-month Average Sales
A	Mar-14	14
A	May-14	25
A	Jun-14	37
A	May-15	33
A	Jun-15	40
B	Apr-14	11
B	May-15	15
B	Jun-15	21

Thanks

[EBGAL · Accepted solution]

This worked for me for a column

VAR Product = Data[Product]

VAR CurrentDate = Data[AnalysisMonth]

VAR EarliestDate = DATE(YEAR(CurrentDate), MONTH(CurrentDate) - 12, DAY(CurrentDate))

RETURN

CALCULATE(

    SUM(Data[Sales]),

    FILTER(ALL(Data), Data[Product] = Product && EarliestDate < Data[AnalysisMonth] && Data[AnalysisMonth] <= CurrentDate)

)

[amitchandak]

@EBGAL , if you have data and a separate date table, prefer separate product dimension too

then rolling like this should work

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

Rolling Months Formula: https://youtu.be/GS5O4G81fww