Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kpangelinan
Helper I
Helper I

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

trailing12.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

DateKey Table

datekey.PNG

1 ACCEPTED SOLUTION

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)

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

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. 

 

Screenshot_1.png

 

 

   

 

Phil_Seamark
Employee
Employee

Hi @kpangelinan

 

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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

@Phil_Seamark Average Monthly 

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)

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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

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)

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.