Forum Discussion

kpangelinan's avatar
kpangelinan
Icon for Helper I rankHelper I
9 years ago
Solved

Calculate Average Trailing 12 Months

I have a column of executed agreement dates (mm/dd/yyy) for dates beginning in 2015-2017. I also have a forecasted arr amount column, for which I would like to calculate the Average trailing 12 month...
Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    9 years ago

    Hi kpangelinan

     

    Did you change the table and column names to suit your date table?

     

     

    Average Forcasted ARR = 
	DIVIDE(
		CALCULATE(
			SUM(Query1[Forecasted ARR Amount])
				,
				DATESBETWEEN(
				'DateKey'[DateKey],
				FIRSTDATE(DATEADD('DateKey'[DateKey],-12,MONTH)),
				LASTDATE('DateKey'[DateKey])
				)
                   ),12)

     

Phil_Seamark's avatar
Phil_Seamark
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

Hi kpangelinan

 

Did you change the table and column names to suit your date table?

 

 

Average Forcasted ARR = 
	DIVIDE(
		CALCULATE(
			SUM(Query1[Forecasted ARR Amount])
				,
				DATESBETWEEN(
				'DateKey'[DateKey],
				FIRSTDATE(DATEADD('DateKey'[DateKey],-12,MONTH)),
				LASTDATE('DateKey'[DateKey])
				)
                   ),12)

 

kpangelinan's avatar
kpangelinan
Icon for Helper I rankHelper I
9 years ago

I did that I just had one minor mistake, but thanks for the solution!