Time Projection Modeling

[crobinson661]

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 current process step and value of the item selected. 

Here is the structure: 

table.Time is the table that tracks the time each process [Step] takes, which varies by cost, which is a range (2 fields [Cost Min]/[Cost Max]) 

Time
UN	Cost Min	Cost Max	Step	Days
1	0	10	Step 1	1
2	0	10	Step 2	3
3	0	10	Step 3	5
4	0	10	Step 4	2
5	11	20	Step 1	1
6	11	20	Step 2	10
7	11	20	Step 3	5
8	11	20	Step 4	2
9	21	30	Step 1	1
10	21	30	Step 2	10
11	21	30	Step 3	5
12	21	30	Step 4	20

table.Item is the selection table where the [Cost] of an [Item] and the current [Step] the [Item] is in is identified when the item is selected

Item
Item	Cost	Step
A	10	Step 1
B	15	Step 2
C	27	Step 4

Here are some examples of the output I would be seeking when each of the items above are selected: 

Return When Item A Selected
Remaining Steps	Projected Date	Projected Date Explained
Step 1	6/8/2024	Based on Item.[Cost] and [Step] = TODAY() + UN1.[Days]
Step 2	6/11/2024	Based on Item.[Cost] and [Step] = TODAY() + (UN1.[Days]+UN2.[Days])
Step 3	6/16/2024	Based on Item.[Cost] and [Step] = TODAY() + (UN1.[Days]+UN2.[Days]+UN3.[Days])
Step 4	6/18/2024	Based on Item.[Cost] and [Step] = TODAY() + (UN1.[Days]+UN2.[Days]+UN3.[Days]+UN4.[Days])
Return When Item B Selected
Remaining Steps	Projected Date	Projected Date Explained
Step 2	6/17/2024	Based on Item.[Cost] and [Step] = TODAY() + (UN6.[Days])
Step 3	6/23/2024	Based on Item.[Cost] and [Step] = TODAY() + (UN6.[Days]+UN7.[Days])
Step 4	6/25/2024	Based on Item.[Cost] and [Step] = TODAY() + (UN6.[Days]+UN7.[Days]+UN8.[Days])
Return When Item C Selected
Remaining Steps	Projected Date	Projected Date Explained
Step 4	6/27/2024	Based on Item.[Cost] and [Step] = TODAY() + (UN12.[Days])

I am unsure how to get the item.[Cost] and item.[Step] of the selected item.[Item] to match to the correct time.[Cost Min]/time.[Cost Max] range AND time.[Step] and return ONLY the current and future steps (see item B & C results above) AND project the dates by adding the time.[Days] from multiple steps. 

[lbendlin]

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

[crobinson661]

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 · Accepted solution]

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]

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)