Forum Discussion

crobinson661's avatar
crobinson661
Frequent Visitor
2 years ago
Solved

Time Projection Modeling

I have a table that tracks how many days each step in a process takes, which varies based on an items value. I need to return the projected date of completion of only the remaining steps based on the...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi crobinson661 ,

     

    Firstly, I think we need to create an unrelated DimStep table.

     

    DimStep = VALUES('Time'[Step])

     

    I suggest you to try code as below to create a measure.

     

    Projected Date = 
VAR _ITEM = MAX('Item'[Item])
VAR _Cost = SUM('Item'[Cost])
VAR _STEP = MAX(DimStep[Step])
VAR _STARTSTEP = MAX('Item'[Step])
VAR _UNDAYS = CALCULATE(SUM('Time'[Days]),FILTER(ALLSELECTED('Time'),'Time'[Cost Min]<=_Cost && 'Time'[Cost Max]>=_Cost && 'Time'[Step]>=_STARTSTEP &&'Time'[Step] <= _STEP))
VAR _TODAY = TODAY() /*This should be TODAY(), here we use 2024.06/07 to have a test.*/
RETURN
IF(_ITEM<>BLANK()&&_STEP>=_STARTSTEP,_TODAY+_UNDAYS)

     

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

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

crobinson661's avatar
crobinson661
Frequent Visitor
2 years ago

I was going to use NETWORKINGDAYS to exclude weekends and in the output examples table above in the [Projected Date Explained] column I demonstrate that the number of NETWORKINGDAYS for each remaining step would be calculated off TODAY(). 

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi crobinson661 ,

 

Firstly, I think we need to create an unrelated DimStep table.

 

DimStep = VALUES('Time'[Step])

 

I suggest you to try code as below to create a measure.

 

Projected Date = 
VAR _ITEM = MAX('Item'[Item])
VAR _Cost = SUM('Item'[Cost])
VAR _STEP = MAX(DimStep[Step])
VAR _STARTSTEP = MAX('Item'[Step])
VAR _UNDAYS = CALCULATE(SUM('Time'[Days]),FILTER(ALLSELECTED('Time'),'Time'[Cost Min]<=_Cost && 'Time'[Cost Max]>=_Cost && 'Time'[Step]>=_STARTSTEP &&'Time'[Step] <= _STEP))
VAR _TODAY = TODAY() /*This should be TODAY(), here we use 2024.06/07 to have a test.*/
RETURN
IF(_ITEM<>BLANK()&&_STEP>=_STARTSTEP,_TODAY+_UNDAYS)

 

Result is as below.

 

Best Regards,
Rico Zhou

 

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

  • crobinson661's avatar
    crobinson661
    Frequent Visitor
    2 years ago

    Sorry for the delay in reply - Thanks for the assistance!!

    I'm finding that this is giving me the resulting estimated completion date of the current [Step] but if there are multiple remaining [Step]s, it is not giving the estimated date of each of those as well. (See Return When Item A and B above)