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 mon...
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

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