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

 

Are you after an Average Daily figure or an Average Monthly figure?

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

    Hi kpangelinan

     

    This might be getting close :)

     

    Average Forcaseted ARR = 
	DIVIDE(
		CALCULATE(
			SUM(Query1[Forecasted ARR Amount])
				,
				DATESBETWEEN(
				'Dates'[Date],
				FIRSTDATE(DATEADD('Dates'[Date],-12,MONTH)),
				LASTDATE('Dates'[Date])
				)
                   ),12)
    • kpangelinan's avatar
      kpangelinan
      Icon for Helper I rankHelper I
      9 years ago

      Phil_Seamark It doesn't seem to work. Specifically, for the 'Dates' [Date], do I need to have the dates formatted a certain way?  So, if I'm looking at January 2017 in a matrix visual - i would like to see ARR for that month, the average of trailing 12 month arr, and possibly break that down even further to each sales rep and what their averages were for trailing 12.

       

      May be a bit too convoluted.

      • 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)