Forum Discussion
Projecting Revenue By Given Factor DAX Help
Hi,
I'm currently looking for a way to project revenue 3 months into the future.
My current formula looks like this:
Sales Forecast =
var SalesLY = CALCULATE(sum('Revenue Dashboard'[REVENUE]), SAMEPERIODLASTYEAR('Calendar'[Date]))
var SalesLY2 = CALCULATE(sum('Revenue Dashboard'[REVENUE]),SAMEPERIODLASTYEAR(dateadd('Calendar'[Date],-1,year)))
var factor =
divide(
divide( CALCULATE(sum('Revenue Dashboard'[REVENUE]), dateadd('Calendar'[Date],-1,MONTH)), CALCULATE(sum('Revenue Dashboard'[REVENUE]),dateadd( dateadd('Calendar'[Date], -1,MONTH),-1,year))) +
divide( CALCULATE(sum('Revenue Dashboard'[REVENUE]), dateadd('Calendar'[Date], -2,MONTH)), CALCULATE(sum('Revenue Dashboard'[REVENUE]),dateadd( dateadd('Calendar'[Date],-2,MONTH),-1,year))) +
divide( CALCULATE(sum('Revenue Dashboard'[REVENUE]), dateadd('Calendar'[Date], -3,MONTH)), CALCULATE(sum('Revenue Dashboard'[REVENUE]),dateadd( dateadd('Calendar'[Date], -3,MONTH),-1,year))),
3, 0)
Return divide(SalesLY + SalesLY2,2,0)*factor
This works great for projecting revenue up to the end of the current month, however the factor variable becomes wildely inaccurate when looking at any future month since it starts adding revenue for months that have not completed yet.
My question is:
Is there a way to "lock" in the factor variable so it stays the same after it reaches today's date?
Please let me know if you need any additional info.
Thanks!
1 Reply
- v-easonf-msftCommunity Support
Hi, joshtrap
The calculated results of measure is always changing in response to your interaction with your reports.
If you have calculated the value of factor, replace the variable ‘ factor’ in the formula with that value as a constant.
Best Regards,
Community Support Team _ Eason