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 months forecasted arr amount. I don't know if this will help, but I also have a separate table (DateKey) which is linked to the ARR table - having created a relationship between the datekey and the executed agreement date columns. Below is a sample of my data set. Help with a DAX formula would be greatly appreciated!

ARR Table

 

 

 

 

 

 

 

 

 

 

 

 

 

 

DateKey Table

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)

     

7 Replies

      • 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)
  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    I have a column of List_Date  (mm/dd/yyy) for dates beginning in 2012-2017. I also have a Sales_Pric column, for which I would like to calculate the 3-month trailing average and 12-month trailing average of the NUMBER of sales/mo. 

     

    We need to use sales as count of sales. 

     

    Please let me know first I need to add one column of count of the sales? or Sales_Pric column can be user directly to find the trailing average.