Forecasting DAX

[Karthik_Ram]

Hello,

We use Line chart with Month in axis and Year in legend and Sales as values. Now I need to do some forecasting logic 

Logic: prior month sales + average % increase for each forecasted month over the last 2 years. 

So if we are in month of May i need to forecast for rest of year. And when we roll over to Jan 2022 it will be for whole year except Jan

Thanks

[Ashish_Mathur]

Hi,

Share some data and show the expected result in a simple Table format.

[Karthik_Ram]

Let me know if this helps

[Ashish_Mathur]

It does not.  I need to study the formulas in your Excel file and then translate them into the DAX language.  So share the download link of your MS Excel file.

[Karthik_Ram]

Forecast_Spreadsheet 

[Ashish_Mathur]

Hi,

In range C31:C37, please use formulas to get those results.

[Karthik_Ram]

https://drive.google.com/file/d/1EIH2ieyzrd3XiiD3h_6VPq1wuoXEpZAK/view?usp=sharing

[Ashish_Mathur]

Hi,

I am trying to come up with an Excel formula first which can calculate the figures in range C32:C37 directly from the figure in cell C30 (rather than from the previous cell).  In cell C32, i tried this formula

=C30+(C30*(1-(AVERAGE(C18/C19,C6/C7)*AVERAGE(C19/C20,C7/C8))))

but my result is 1047.024 and yours is 1006.548.  Can you help me with a formula in range C32:C37 which takes the input from cell C30 (rather than from the previous cell).  This will make the DAX formula writing easier/possible.

[Karthik_Ram]

We need to calculate values for May when in may and also End of month values for remaining months in year. So two columns basically. Below is formula
(((2020 %Chg + 2019 %chg)/2)* Total at End of Month last Month) + Total at End of Month last Month

[Ashish_Mathur]

Hi,

That's where my struggle is.  I cannot write a measure or a calculated column formula to refer to the previous cell but i can possible write a measure if for all forecast months, the input cell is of the last known month i.e. April.  So can you think of some mathematical logic to get the result that you have shown in the Excel file by taking the inpu as April?

[Karthik_Ram]

Do you have dax or calculation in Dax if we do last known month? For instance Two columns one for this month rest of days we can do based off on May 5th value and for Rest of year value we can do using Apr31st value? I can see if it closely matches to my logic calculation or run by users to see if that works.

[Ashish_Mathur]

Hi,

I do not understand your response.  Perhaps someone else will help you with this.

[Karthik_Ram]

Ashish,

Sorry if my earlier response was unclear. 

"i can possible write a measure if for all forecast months, the input cell is of the last known month i.e. April." 

Can you write dax using that assumption you had mentioned in quotes so that I can run by user to see if that is accepted.

[Ashish_Mathur]

I do not know what growth rate to use month on month.  I need that inout from you.  In an MS Excel file, show the formula assuming the last know month's sales as the input value.

[Karthik_Ram]

Ashish,

Check the link again. So sales and forecast are different. So for months thats not yet occured we need to take prior month so for June it would be forecasted May value and for July it will be forecasted June value.