Forum Discussion

Whitewater100's avatar
Whitewater100
Icon for Solution Sage rankSolution Sage
4 years ago
Solved

DAX Help on Factoring in Lead Time for Forecasting

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 l...
  • Whitewater100's avatar
    Whitewater100
    4 years ago

    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
Whitewater100's avatar
Whitewater100
Icon for Solution Sage rankSolution Sage
4 years ago

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