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]) TimeUNCost MinCost Max Step Days1010Step 112010Step 233010Step 354010Step 4251120Step 1161120Step 21071120Step 3581120Step 4292130Step 11102130Step 210112130Step 35122130Step 420 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 selectedItemItem Cost Step A10Step 1B15Step 2C27Step 4 Here are some examples of the output I would be seeking when each of the items above are selected: Return When Item A SelectedRemaining StepsProjected DateProjected Date ExplainedStep 16/8/2024Based on Item.[Cost] and [Step] = TODAY() + UN1.[Days]Step 26/11/2024Based on Item.[Cost] and [Step] = TODAY() + (UN1.[Days]+UN2.[Days])Step 36/16/2024Based on Item.[Cost] and [Step] = TODAY() + (UN1.[Days]+UN2.[Days]+UN3.[Days])Step 46/18/2024Based on Item.[Cost] and [Step] = TODAY() + (UN1.[Days]+UN2.[Days]+UN3.[Days]+UN4.[Days]) Return When Item B SelectedRemaining StepsProjected DateProjected Date ExplainedStep 26/17/2024Based on Item.[Cost] and [Step] = TODAY() + (UN6.[Days])Step 36/23/2024Based on Item.[Cost] and [Step] = TODAY() + (UN6.[Days]+UN7.[Days])Step 46/25/2024Based on Item.[Cost] and [Step] = TODAY() + (UN6.[Days]+UN7.[Days]+UN8.[Days]) Return When Item C SelectedRemaining StepsProjected DateProjected Date ExplainedStep 46/27/2024Based 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.

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

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 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.

Need Help

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.

Test.pbix37 KB

4 Replies

lbendlin
Super User
2 years ago
what is your definition of "days"? How about weekends and holidays? Does your source data have actual starting dates?
- 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
    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.
    Test.pbix37 KB

Forum Discussion

Time Projection Modeling

4 Replies

Related content

Semantic model Refresh Time Setup

PowerBI Model time dependant

PowerBI Semantic model Refresh taking long time

Missing Project Name

Semantic Model Takes a Long Time To Refresh