topic Re: I want to generate a series with multiple start and end values in DAX Commands and Tips

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

Madhu9a — Sat, 01 Jun 2024 06:04:20 GMT

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

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

BeaBF — Fri, 31 May 2024 13:33:26 GMT

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

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

xifeng_L — Fri, 31 May 2024 13:34:37 GMT

Madhu9a

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

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

Madhu9a — Sat, 01 Jun 2024 06:46:33 GMT

Modified Original post with explanation

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

xifeng_L — Sat, 01 Jun 2024 09:27:19 GMT

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~

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

Madhu9a — Sat, 01 Jun 2024 15:40:11 GMT

Thank you very much 🙏