Getting particular value of 12 month rolling average

[Anonymous]

I am able to get the 12-month rolling average using this formula:

Amount-12MRAvg = 
IF(
	ISFILTERED('Orders'[Date]),
	ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
    IF(
        MIN('Orders'[Amount]) <> BLANK(), 
        VAR __LAST_DATE = ENDOFMONTH('Calendar'[Date].[Date])
        VAR __DATE_PERIOD =
            DATESBETWEEN(
                'Calendar'[Date].[Date],
                STARTOFMONTH(DATEADD(__LAST_DATE, -11, MONTH)),
                __LAST_DATE
            )
        RETURN
            AVERAGEX(
                CALCULATETABLE(
                    SUMMARIZE(
                        VALUES('Orders'),
                        'Calendar'[Date].[Year],
                        'Calendar'[Date].[QuarterNo],
                        'Calendar'[Date].[Quarter],
                        'Calendar'[Date].[MonthNo],
                        'Calendar'[Date].[Month]
                    ),
                    __DATE_PERIOD
                ),
                CALCULATE(SUM('Orders'[Amount]), ALL('Calendar'[Date].[Day]))
            ), 
        BLANK()
    )
)

I am able to plot a line chart with the Calendar[Date] as the axis and the Rolling Average as the value. But I have a requirement to use the 12-month rolling average values of two different attributes and divide them. How do I do that?

When I try to just show the current month's 12-month rolling average value, I only get a Blank cell. Why is that?

Ideally, if I can get the current month's 12-month rolling average values for the two attributes (Orders and Assets), then I can divide the values to show the actual metric. What am I doing wrong?

Thanks.

[Tanushree_Kapse · Accepted solution]

Hi @Anonymous ,

Try this measure to calculate the Rollig average for 12 month:

Measure= 

 Var Rollingsum= CALCULATE(SUM('Orders'[Amount]), DATESINPERIOD(Calender[Date], LASTDATE(Calender[Date]), -12, MONTH))

RETURN  DIVIDE(Rollingsum, 12)

I hope this helps!

Mark this as a solution if I answered your question. Kudos are always appreciated.

Thanks!