topic Re: Spread Forecast Based on Monthly Seasonal % - measure almost complete! in DAX Commands and Tips

Spread Forecast Based on Monthly Seasonal % - measure almost complete!

Whitewater100 — Fri, 14 Jan 2022 18:58:42 GMT

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

Dear Community:

I am trying to get the last part of a DAX forumla to work dynamically. In this measure:

Revised Forecast =

var SumBal = SUMX(SUMMARIZE(filter(ALLSELECTED(Dates),Dates[MonthOfYear] in {9,10,11,12}),Dates[MonthOfYear],"Seasonal Bal",[Seasonal Bal % Mix]),[Seasonal Bal])

var Remainder = [Remaining FCast]

var Final = divide([Remaining FCast] * [Seasonal Bal % Mix],sumbal)

return

if(ISBLANK([Seasonal Bal % Mix]),blank(),Final)

I want to have the remaining months seasonal percents added together dynamically. They are hard coded above in {9,10,11,12}

becasue we have actual results thru August(MO #8).

I have provided a link to the file. This mesure is currently giving the correct answer but is only good if results are through August.

Thanks in advance for any assist on this one!

Re: Spread Forecast Based on Monthly Seasonal % - measure almost complete!

AlexisOlson — Fri, 14 Jan 2022 20:00:18 GMT

You could use a variable like this:

Revised Forecast = VAR CurrMonth = MAX ( Dates[MonthOfYear] ) VAR SumBal = SUMX ( SUMMARIZE ( FILTER ( ALLSELECTED ( Dates ), Dates[MonthOfYear] > CurrMonth ), Dates[MonthOfYear], "Seasonal Bal", [Seasonal Bal % Mix] ), [Seasonal Bal] ) VAR Remainder = [Remaining FCast] VAR Final = DIVIDE ( [Remaining FCast] * [Seasonal Bal % Mix], sumbal ) RETURN IF ( ISBLANK ( [Seasonal Bal % Mix] ), BLANK (), Final )

Re: Spread Forecast Based on Monthly Seasonal % - measure almost complete!

Whitewater100 — Sun, 16 Jan 2022 15:26:25 GMT

Hi Alexis Olson:

Thanks very much for your work on this measure. It's providing results for Sept-Nov but December is blank. The results appear to be inflated. I will attempt to paste in the expected results and the steps the measure is trying to cover. This definitely got it a step closer and would appreicate if you could take another look. Your meassge is in the matrix all the way to the right.

Thanks again!

Re: Spread Forecast Based on Monthly Seasonal % - measure almost complete!

AlexisOlson — Sun, 16 Jan 2022 18:25:40 GMT

I think I understand a bit better now. If you can calculate the last month with sales, then you can create the list of remaining months as GENERATESERIES ( LastSalesMonth + 1, 12 )

I feel like there should be a bit cleaner way of defining LastSalesMonth but give this a try:

Revised Forecast = VAR LastSalesMonth = MAXX ( FILTER ( ALL ( Dates[MonthOfYear] ), NOT ISBLANK ( CALCULATE ( [EC Sales Demo], ALLEXCEPT ( Dates, Dates[MonthOfYear] ) ) ) ), Dates[MonthOfYear] ) VAR RemainingMonths = GENERATESERIES ( LastSalesMonth + 1, 12 ) VAR SumBal = SUMX ( SUMMARIZE ( FILTER ( ALLSELECTED ( Dates ), Dates[MonthOfYear] IN RemainingMonths ), Dates[MonthOfYear], "Seasonal Bal", [Seasonal Bal % Mix] ), [Seasonal Bal] ) VAR SeasonalBalMix = [Seasonal Bal % Mix] VAR Final = DIVIDE ( [Remaining FCast] * SeasonalBalMix, SumBal ) RETURN IF ( ISBLANK ( SeasonalBalMix ), BLANK (), Final )

Re: Spread Forecast Based on Monthly Seasonal % - measure almost complete!

Whitewater100 — Sun, 16 Jan 2022 19:25:35 GMT

Hi AlexisOlson:

That was great! Thanks very much, made my day/week.

Have a nice Sunday!