Dynamic forecast from actuals and budget

[vincentnoot]

Hi all,

I'm trying to create a dynamic forecast based off budget and actuals.

The table below shows what I hope to achieve in the column on the right;

Month	Revenue	Budget	Diff	Forecast
jan	240.000	210.000	30.000	210.000
feb	200.000	180.000	20.000	210.000
maa		210.000		260.000
apr		175.000		225.000
mei		219.000		269.000
jun		280.000		330.000
jul		265.000		315.000
aug		204.000		254.000
sep		220.000		270.000
okt		250.000		300.000
nov		230.000		280.000
dec		220.000		270.000

Budgetdiff is AmountRevenue - BudgetRevenue

As shown, the forecast should be budget + SUM(budgetdiff)

The problem is, AmountRevenue, Budget and BudgetDiff are all measures, and when I try to create my forecast measure, the result always adds the rows by month instead of the TOTAL sum of BudgetDif for the selected year.

I've tried this: 

Measure = [Budget]  + CALCULATE([BudgetDif], ALL(dimdate[MonthName]), FILTER(dimdate, dimdate[Year]))

but it doesn't work.

If anyone has an idea, it would be greatly appreciated, I've been stuck on this for days now!

Thanks 🙂

[amitchandak · Accepted solution]

@vincentnoot , Try like

Measure = [Budget] + CALCULATE(sumx(filter(values(dimdate[month]),not(isblank([Revenue]))),all(dimdate))

[amitchandak · Accepted solution]

@vincentnoot , Try like

Measure = [Budget] + CALCULATE(sumx(filter(values(dimdate[month]),not(isblank([Revenue]))),all(dimdate))

[vincentnoot · Accepted solution]

Hi @amitchandak ,

Your formula helped me get the right results! Thank you so much!

I had to change your formula a little bit into

Forecast = Budget + CALCULATE(SUMX(VALUES(dimdate[Month]), not(isblank([Revenue])), ALLEXCEPT(dimdate, dimdate[year]))