Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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)
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.
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)
@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)
I did that I just had one minor mistake, but thanks for the solution!
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |