I want to generate a series with multiple start and end values

[Madhu9a]

I have a starting data table in DAX 

Task	Initial Interval	Repeat Interval	Initial Life	Final Life
Task_1	3	2	6	20
Task_2	4	3	7	18

I want to use generateseries to repeat the task values starting with Initial Interval and then increment by Repeat Interval till its less than or equal to Initial life then start increment by Initial Interval and then increment  by Repeat Interval till Inital life or Final Life which ever comes first. As listed in the table below

Task	Initial Interval	Repeat Interval	Initial Life	Final Life	Series	Life Iteration	Series Explained
Task_1	3	2	6	20	3	Life_1	Starts with 3 (Initial Interval)
Task_1	3	2	6	20	5	Life_1	3+ 2 (Add Repeat Interval)
Task_1	3	2	6	20	6	Life_1	Add Repeat Interval 5 + 2 = 7 is greater than Initial life 6. so take 6
Task_1	3	2	6	20	9	Life_2	Starting task_1 with Life_2   6 + 3 = 9
Task_1	3	2	6	20	11	Life_2	9 + 2 (Repeat Interval) = 11
Task_1	3	2	6	20	12	Life_2	adding (Repeat Interval) 11 + 2= 13  but is greater than 12 (Multiple of 6 Initial Life)  so take 12
Task_1	3	2	6	20	15	Life_3	starting Task_1 with Life_3  12 + 3 = 15
Task_1	3	2	6	20	17	Life_3	15 + 2 (Repeat Interval) = 17
Task_1	3	2	6	20	18	Life_3	17 + 2 = 19 is greater than 18 multiple of 6 Initial life
Task_1	3	2	6	20	20	Life_4	Starting Task_1 with Life_4 18 + 3 = 21 greater than 20. so take 20 End of Task_1
Task_2	4	3	7	18	4	Life_1	Similar process for Task_2
Task_2	4	3	7	18	7	Life_1
Task_2	4	3	7	18	11	Life_2
Task_2	4	3	7	18	14	Life_2
Task_2	4	3	7	18	18	Life_3

We need not create columns Life Iteration and Series Explained. Only Series column is required. How can we create this sort of series. Please help

[xifeng_L]

@Madhu9a 

I can't understand your logic. Can you be more detailed?

[Madhu9a]

Modified Original post with explanation

[xifeng_L · Accepted solution]

Hi @Madhu9a 

You can try below calculated table / evaluate expression.

EVALUATE
GENERATE(
    'Tasks',
    DISTINCT(
        SELECTCOLUMNS(
            GENERATESERIES(1,MAX('Tasks'[Final Life])),
            "Series",
                VAR i = [Value]
                VAR LifeItemNbr = ROUNDUP(DIVIDE('Tasks'[Initial Life]-'Tasks'[Initial Interval],'Tasks'[Repeat Interval]),0)+1
                VAR LifeNbr = ROUNDUP(DIVIDE(i,LifeItemNbr),0)
                VAR j = MOD(i-1,LifeItemNbr)
                RETURN
                MIN(
                    MIN(
                        'Tasks'[Initial Life]*(LifeNbr-1)+'Tasks'[Initial Interval]+'Tasks'[Repeat Interval]*j,
                        'Tasks'[Initial Life]*LifeNbr
                    ),
                    'Tasks'[Final Life]
                )
        )
    )
)
ORDER BY 'Tasks'[Task],[Series]

Demo - I want to generate a series with multiple start and end values.pbix

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

[Madhu9a]

Thank you very much 🙏

[BeaBF]

@Madhu9a Hi! Try with this calculated table:

CalculatedTable =

VAR AllTasks =

    GENERATE (

        Tasks,

        VAR Series =

            GENERATESERIES ( [Initial Life], [Final Life], [Repeat Interval] )

        RETURN

            ADDCOLUMNS (

                Series,

                "Task_Name", Tasks[Task],

                "Initial_Interval", Tasks[Initial Interval],

                "Repeat_Interval", Tasks[Repeat Interval],

                "Iteration_Life", "Life_" & ( ( [Value] - Tasks[Initial Life] ) / Tasks[Initial Interval] + 1 )

            )

    )

RETURN

    AllTasks