Revised Forecast Allocation

DAX DAX Calculations
1

Hello Forum Community:

I am looking for a bit of help solving a calculation regarding reallocation of a forecast using a particular technique. I will attach my working model and the two key tables involved in this calculation are
“PQ_EC_2021_Budget” and “Ecomm_Sls_FC_Comp”.

On the attached file I have labeled the page “PROBLEM” and have laid out the calculation desired and desired results.

Essentially I am wanting to do the following:
Spread the remained of forecast by multiplying the original monthly seasonality percent and then dividing by the entire of sum of the remaining original seasonality. This is shown step by step on the Problem page. My measure so far looks like this:
TEST Forecast Reallocation =
VAR forecaststartmonth =
CALCULATE (
MIN ( PQ_EC_2021_Budget[Month & Year] ),
FILTER ( ALL ( PQ_EC_2021_Budget[Month & Year]), [EC Sales Demo] = 0 )
)
VAR budgettotal =
CALCULATE ( [Allocation FCast Ret$ Daily], REMOVEFILTERS () )
VAR achievedtotal =
CALCULATE ( [EC Sales Demo], REMOVEFILTERS () )
VAR currentmix = [Seasonal % Mix]
VAR newtable =
FILTER (
ADDCOLUMNS ( ALL ( PQ_EC_2021_Budget[Month & Year] ), “@Mix”, [Seasonal % Mix] ),
PQ_EC_2021_Budget[Month & Year] >= forecaststartmonth
)
VAR mix_balofyear =
SUMX ( newtable, [@Mix] )
RETURN
IF (

    MAX ( Data[Month] ) >= forecaststartmonth,

    ( budgettotal - achievedtotal ) * currentmix / mix_balanceofyear

)

I’m having particular trouble summing up the remainder of year original seasonality.

Thanks in advance for any help on this question!

Whitewater
Sales_Project.pbix (2.5 MB)

2

Hi @Whitewater100

Looking at the Problem statement, I have come up with below measure. This is doing what is expected and mentioned in Problem statement. Only thing it is taking values for Sept-Dec as hard coded rather then derived. Let me know if it is helpful or any changes required.

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)

image
Sales_Project.pbix (2.5 MB)

Thanks
Ankit J

3

Hi Ankit J:

First of all thank you for working on this problem. You have arrived at the correct answer for this particular scenario but I’m hoping that we wouldn’t hard code(as you mentioned). I’ve been trying all sorts of things but am not successful yet.

If you have time it would be greatly appreciated if we can make the entire calculation dynamic.

Thanks so much!!

Bill

I will attach some pieces of the formula that appear to work individually but not all together.
partsofmeasure for spreading remaining forecast.docx (15.1 KB)

4

Hi @Whitewater100

Can try below measure. It is dynamic and provide same results.

Balance Mix = 
VAR forecaststartmonth =
    CALCULATE (
        min(Dates[MonthnYear]),
        FILTER ( ALLSELECTED( Dates), ISBLANK([EC Sales Demo]) )
    )
VAR budgettotal =
    CALCULATE( [Total '21 Budget], REMOVEFILTERS())
VAR achievedtotal =
    CALCULATE ( [EC Sales Demo], REMOVEFILTERS () )
VAR currentmix = [Seasonal % Mix]
VAR newtable =
   SUMMARIZE (filter(ALLSELECTED(dates),Dates[MonthnYear] >= forecaststartmonth),
    Dates[Month & Year],
    "Seasonal Bal Mix", [Seasonal Bal % Mix])
VAR mixsum_after_achieved =

    sumx( newtable,[Seasonal Bal Mix])
RETURN
    if(isblank([EC Sales Demo]),mixsum_after_achieved,blank())

Thanks
Ankit J

5

Hi Ankit:

Thanks very much for your continued help on this one. I was able to get some additional logic and now the measure is fully dynamic. The code I have is directly below. If you have any thoughts on this next question it would be great. I’m trying to get a total figure for Revised forecast but it doesn’t do that, just monthly values from Sept-Dec. Do you know how to get a total out of this?

Thanks again!! Bill

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 )

6

Hi @Whitewater100 - Good to know your main issue is resolved.

As it is a separate request, may be close this post and create a new one with new requirement.

Thanks
Ankit J

7

Hi Ankit:

Yes we can close this one. I appreciate your help!

Best regards,

Billl

1 Like