Issue with 12 month moving average formula - Need Help

[dmalviya]

Hi,

i am facing strange issue on calculating 12 month moving average. it is not summing values for last 12 month, instead only for current month & dividing by 12. However it is calculating 12 month correctly. I tried 3 different version but none of them are working.

Version 1 -

12 Month Sales Moving Average =
CALCULATE (
    SUM(Sales[Amount]),
    DATESBETWEEN (
        CalendarDate[Date],
        NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( CalendarDate[Date].[Date] ) ) ),
        LASTDATE ( CalendarDate[Date].[Date] )
    )
)
    / [Number of periods]

---------------------------------------------------------------

Version 2

12 Month Sales Moving Average =
CALCULATE(CALCULATE (
    SUMx(Sales,Sales[Amount]),FILTER(CalendarDate, CalendarDate[Date].[Date] >= NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( CalendarDate[Date].[Date] ) ) )
    && CalendarDate[Date].[Date] <= LASTDATE ( CalendarDate[Date].[Date] )
    )
)
    / [Number of periods])

--------------------------------------------------

Version 3

12 Month Sales Moving Average =
CALCULATE ( SUMX(Sales,Sales[Amount]),     
    DATESBETWEEN (
        CalendarDate[Date],
        NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( CalendarDate[Date] ) ) ),
        LASTDATE ( CalendarDate[Date])
    )
) / [Number of periods]

Please find attached sample file here -

https://1drv.ms/u/s!AgZ1uNPRA6n_g3ko7nmSnr0KNkU9

Thanks

Deepak

[v-danhe-msft · Accepted solution]

Hi @dmalviya,

Based on my test, you could refer to below steps:

I have created a date table and create relationship:

Date = CALENDARAUTO()

Create the measure:

MAT Sales = 
CALCULATE (
	SUM('Sales'[Amount]),
	DATESINPERIOD (
		'Date'[Date],
		LASTDATE ( 'Date'[Date] ),
		-1,
		YEAR
	)
)

Result:

You could also download the pbix file to have a view.

Regards,

Daniel He