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.

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
2 years ago

what is your definition of "days"?  How about weekends and holidays? Does your source data have actual starting dates?

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)