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

[Whitewater100]

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!

[AlexisOlson]

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 )

[Whitewater100]

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!

[AlexisOlson · Accepted solution]

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 )

[Whitewater100]

Hi AlexisOlson:

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

Have a nice Sunday!