Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
@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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
21 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |