Forum Discussion

joshtrap's avatar
joshtrap
Frequent Visitor
4 years ago

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-msft's avatar
    v-easonf-msft
    Community 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