topic Re: Forward Forecast to achieve Budget for the Year in DAX Commands and Tips

Forward Forecast to achieve Budget for the Year

nkt — Sun, 06 Mar 2022 23:42:55 GMT

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?

Re: Forward Forecast to achieve Budget for the Year

amitchandak — Mon, 07 Mar 2022 05:51:45 GMT

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])))

Re: Forward Forecast to achieve Budget for the Year

nkt — Mon, 07 Mar 2022 23:31:26 GMT

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.

Re: Forward Forecast to achieve Budget for the Year

Icey — Tue, 15 Mar 2022 09:17:14 GMT

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

Re: Forward Forecast to achieve Budget for the Year

nkt — Thu, 17 Mar 2022 04:09:29 GMT

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?

Re: Forward Forecast to achieve Budget for the Year

Icey — Thu, 17 Mar 2022 06:24:22 GMT

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

Re: Forward Forecast to achieve Budget for the Year

nkt — Fri, 18 Mar 2022 02:56:47 GMT

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

Re: Forward Forecast to achieve Budget for the Year

Icey — Fri, 18 Mar 2022 09:14:08 GMT

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