Forecast Reallocation - Considering lead times

[Whitewater100]

Hello:

I could use some input on creating a DAX forecast reallocation calculation that considers a SelectedValue lead time. The formula below works fime for re-allocating the forecast for any future dates (where sales have not occured) and also accomodates a factor to consider changes to the original forecast. This is the measure being used currently.

Adj '21 FCast QUESTION =

VAR LastOrderDate =

CALCULATE(

MAX( 'ecomm_Sls_FC_Comp'[transaction_date] ),

ALL( 'ecomm_Sls_FC_Comp' )

)

VAR AdjustedForecast =

SUMX(

'Dates',

IF(

'Dates'[Date] <= LastOrderDate,

[EC Sales Demo],

[Total '21 Budget] *1.1

)

)

RETURN

AdjustedForecast

When introducing a slicer for lead time days, what should happpen is the original(latest) forecast can't change during that period but can after that period is over. In this image (green column) if a lead time of 30 days were selected than the forecast for September would stay put at $74,875 but would go to $130K for October as a growth factor of 10% was added into the measure above. My date table is robust, this ficticious ecommerce forecast is at day and product grain. I will place an image of table relationships if needed. Besides the date table only two other tables are involved. PQ_EC-2021 Budget & Ecomm_Sls_FC_Comp.

Thank you! Bill

[v-robertq-msft]

Hi, 

According to your description, I can roughly understand what you want to get. But I find it hard to create such a large quantity of data based on your data model. Would you like to post some sample data in table form or pbix file(without sensitive data) and your expected result(like the chart you want to get and the correct measure value based on your sample data)?

Thanks very much!

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Whitewater100 · Accepted solution]

Hi Robert:

Thank you for replying. After a while, I beleive I figured it out. I'll paste the measure here. I'm all set and I appreciate your follow-up!

Best regards

Adj '21 FCast Adj% and Lead Time =

VAR LastOrderDate =

CALCULATE(

MAX( 'ecomm_Sls_FC_Comp'[transaction_date] ),

ALL( 'ecomm_Sls_FC_Comp' )

)

VAR fixedfrozenhorizon = CALCULATE(

[Total '21 Budget],

DATESINPERIOD(Dates[Date],

MAX(ecomm_Sls_FC_Comp[transaction_date]),

SELECTEDVALUE('Lead Time'[Value]),DAY))

VAR LeadTimeDays = SELECTEDVALUE('Lead Time'[Value], 30)

VAR AdjustedForecast =

SUMX(

'Dates',

IF(

'Dates'[Date] <= LastOrderDate,

[EC Sales Demo],

IF(

'Dates'[Date] > LastOrderDate + LeadTimeDays,

[Total '21 Budget] *(1 + [FCast Adj %]),

[Total '21 Budget])

)

)

RETURN

AdjustedForecast