Rollinh 12 month of comulative shipments

[Ortignano]

Hello,

I have shipments table with a cumulative shipments defined as:

=CALCULATE(SUM(Shipments[ShipQty]);FILTER(ALL(DateCalendarMonthID);DateCalendarMonthID[MonthID]<=MAX(DateCalendarMonthID[MonthID])))

Now I would calculate the average of the last 12 cumulative month

I try with 

=CALCULATE([cumulative shipments]);FILTER (
ALL ( DateCalendarMonthID );
DateCalendarMonthID[MonthID] <= MAX ( DateCalendarMonthID[MonthID] )
&& DateCalendarMonthID[MonthID]
>= MAX ( DateCalendarMonthID[MonthID] ) - 11
)
)/12

but it returns again the last cumulative value divided by 12 and not the sum of last 12 months divided by 12

Any suggestion?

Thanks

Antonio

[amitchandak]

@Ortignano , Try a formula like the given example with date table

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

[Ortignano]

Sorry I was not so clear, your formula calculates the sum of last 12 month sales, instead, I would calculate the sum of last 12-month cumulative.

like

[Ortignano]

What I have found till now is to use:

RunningArea =
VAR MyTable = SUMMARIZE(FILTER(ALL(DateCalendarMonthID);DateCalendarMonthID[Date]<=MAX(DateCalendarMonthID[Date])&& DateCalendarMonthID[Date]>=MAX(DateCalendarMonthID[Date])-11);DateCalendarMonthID[Date];"Comulative_shipments";[Comulative_shipments])
RETURN SUMX(MyTable;[Comulative_shipments])

But it give me the overall sum of the comulative_shipments and I'm not able to make the formula works only for last 12 months