Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Need Help in trailing average

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.

 

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Here is a 12 month trailing average that i've been using.  This requires a Date Table which includes a column called "YEARMONTH" which is simply a concatination of year and month.  I.e. January 2017 = "201701"

     

    Measure 12 Mth Avg = CALCULATE( 
	if(
		countrows(values('Dim - Date Table'[YearMonth])) = 1, 
		[Your Measure], 
		AVERAGEX(
			values('Dim - Date Table'[YearMonth]), 
			[YourMeasure]
		)
	),
	DATESINPERIOD(
		'Dim - Date Table'[Date], 
		LASTDATE('Dim - Date Table'[Date]), 
		-12, 
		MONTH
	)
)


    Would this work for you?

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Here is a 12 month trailing average that i've been using.  This requires a Date Table which includes a column called "YEARMONTH" which is simply a concatination of year and month.  I.e. January 2017 = "201701"

     

    Measure 12 Mth Avg = CALCULATE( 
	if(
		countrows(values('Dim - Date Table'[YearMonth])) = 1, 
		[Your Measure], 
		AVERAGEX(
			values('Dim - Date Table'[YearMonth]), 
			[YourMeasure]
		)
	),
	DATESINPERIOD(
		'Dim - Date Table'[Date], 
		LASTDATE('Dim - Date Table'[Date]), 
		-12, 
		MONTH
	)
)


    Would this work for you?