Current month vs Previous month Forecast difference

[kjohn10]

I've looked through various posts here, and I cannot seem to get any of the DAX or 'Quick Calcs' to work right for calculating the current months forecast value versus that month's forecast value last month. 

Sample data below; Each month a new forecast is released (ForecastDate), and it starts with the current month (ForecastMonths) and goes out 12 months (just showing first 3 months here).

Taking SKU 'abc123' for example, for the initial forecast (ForecastDate 2017-01-01) for ForecastMonths 2017-02-01 (next month) units is 20. Then next months forecast, ForecastDate=2017-02-01, for ForecastMonths 2017-02-01, units is 25. The difference is +5 units from last months forecast for this month. Whats the right way to get this calculated...measure? column? I've tried multiple different measure calculations based on various posts, I could not get any to work out.

[Ashish_Mathur · Accepted solution]

Hi,

You may refer to my solution here.

Hope this helps.

[kjohn10]

Yes, thank you that helped. Looking between what I had tried, and your solution, I noted that I was associating my Date table date to the data ForecastDate, instead of ForecastMonths, which caused my data to not calculate.

For those who may not want to download the solution, here is the basics to complete this; using data above and calling it 'Data', and having a generated Date table with range of appropriate dates:

Next forecast = CALCULATE(SUM(Data[Units]),FILTER(Data,Data[ForecastDate]=Data[ForecastMonths]))

Previous forecast = if(ISBLANK([Next forecast]),BLANK(),CALCULATE(SUM(Data[Units]),FILTER(Data,EDATE(Data[ForecastMonths],-1)=[ForecastDate])))

Variance = if(ISBLANK([Previous forecast]),BLANK(),[Next forecast]-[Previous forecast])

Then just use the Date table Year and Month columns (generated from the main date column), and ensure to associate the Date column to the ForecastMonths.

[Ashish_Mathur]

You are welcome.