Forum Discussion

dmalviya's avatar
dmalviya
Frequent Visitor
7 years ago
Solved

Issue with 12 month moving average formula - Need Help

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

Need Help
  • v-danhe-msft's avatar
    v-danhe-msft
    7 years ago

    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

1 Reply

  • v-danhe-msft's avatar
    v-danhe-msft
    Microsoft Employee
    7 years ago

    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