Forward Forecast to achieve Budget for the Year

[nkt]

I have the below table in Power Bi and I want to add a forecast column to calculate what we need to take for the remainder of the year to achieve the total year budget. Is this possible to account for missed sales so far YTD?

[nkt]

Thank you for coming back however I'm trying to replicate the below in Power Bi. I have built what I would like to see in excel but I can't quite work out how to get the forecast figure in Power Bi.

So the forecast is based on the sales we need minus achieved sales to date. This means the forecast number and the achieved number totals to the $3m budget number. 

[Icey]

Hi @nkt ,

---

I don't quite understand the calculation logic of "Forecast" column. Could you explain a little more detail for me?

For example,

233,990 = ?

211,552 = ?

Best Regards,

Icey

[nkt]

Hi Icey, 

The forecast is taking into account the missed sales from Jan and Feb split out across the remaining months based on sales mix. In the below example to get the forecast figure I have used the formula =$H$15*D5/SUM($D$5:$D$14)

For example; the Jan and Feb budget combined is $357k, however we only achieved $157,533 so we missed budget by $199,467.

To meet our annual budget of $3m I need to add this miss of $199,467 to the forecast split across the remaining months as the above example. Is this possible in Power Bi?

[Icey]

Hi @nkt ,

I basically understand your needs.

And another question, what does "H15" stand for in your formula? What is the value of this cell? And Does "D5" to "D14" mean the forecast value from March to December?

Best Regards,

Icey

[nkt]

Apologies, I should've copied in the column headers! Please see below:

[Icey]

Hi @nkt ,

What does "H15" stand for in your formula? What is the value of this cell?

>> In the below example to get the forecast figure I have used the formula =$H$15*D5/SUM($D$5:$D$14)

If I don't misunderstand, in your scenario,

D5 =$H$15*D5/SUM($D$5:$D$14)

=> 1 =$H$15/SUM($D$5:$D$14)

=> $H$15 = SUM($D$5:$D$14) 

This formula cannot calculate the value of D5. Am I missing something?

Best Regards,

Icey

[amitchandak]

@nkt , You can get this year value like this

This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))

This Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))