DAX Help on Factoring in Lead Time for Forecasting

Whitewater100 — Mon, 10 Jan 2022 14:47:59 GMT

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

Hello:

I am hoping to get some input on revising this forecast measure to account for a SelectedValue for lead time. If a lead time choosen is for 30 days, then the upcoming forecast would not be able to be altered for 30 days. After the 30 days the "Adjusted" forecast can be added to the actual sales which have occured. In the picture below the lead time is for 30 days. This means the month of September would stay at &74,875 (orange highlight) but Oct-Dec can change to the green highlight. I will paste my measure below and I believe it just needs a clause that just maintains the initial forecast for as many days that are selected for lead time, then the forecast can be adjusted. I have included the pbix I made via link, Thank you!

PS. [EC Sales Demo] is just a sales measure.

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],

// I beleive this is where the additional logic would go

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

)

RETURN

AdjustedForecast

Re: DAX Help on Factoring in Lead Time for Forecasting

Whitewater100 — Mon, 10 Jan 2022 17:17:03 GMT

I ended up solving this. In case the solution is helpful to others:

Adj '21 FCast QUESTION =

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

topic DAX Help on Factoring in Lead Time for Forecasting in DAX Commands and Tips

DAX Help on Factoring in Lead Time for Forecasting

Re: DAX Help on Factoring in Lead Time for Forecasting