Forum Discussion
Generateseries of multiple values
Hi Power BI Community,
I'd like to generateseries from the table below using the month number as the input value of end_value in the syntax below.
GENERATESERIES ( start_value, end_value [, step_value] )
| Lease # | Start date | End date |
Scheduled payments USD |
Months |
| 1 | 7/20/2024 | 7/19/2029 | 4,000 | 60 |
| 2 | 1/1/2023 | 12/31/2024 | 2,000 | 23 |
Please could you let me konw how I can dynamically generate a table of multiple generated series referencing [Months] column in the table.
Thank you for your help!
Best regards,
This topic was solved by simplifying the code in the earlier topic as follows:
Series = GENERATE( VALUES('Tasks'[Task]), -- Iterate over distinct Task Names VAR MaxFinalLife = MAXX(FILTER('Tasks', 'Tasks'[Task] = EARLIER('Tasks'[Task])), 'Tasks'[Final Life]) RETURN SELECTCOLUMNS( GENERATESERIES(1, MaxFinalLife, 1), "Series", [Value] ) )Hi DataNinja777 ,
This formula refers to an existing table.
Series = VAR __TBL = SELECTCOLUMNS('Data', "Start Date", 'Data'[Start Date], "End Date", 'Data'[End Date], "Index", 'Data'[Index] ) VAR __MAX = MAX ( Data[Month] ) VAR __Series = GENERATESERIES ( 1, __MAX, 1 ) --must use the highest month count VAR __CROSSJOINED = ADDCOLUMNS ( CROSSJOIN ( __TBL, __Series ), "Date", EDATE ( [Start Date], [Value] ) - 1 ) -- this will generate dates way later than the end date RETURN FILTER ( __CROSSJOINED, [Date] <= [End Date] )Index is not necessary. It is just a way for me to identify which row from the referenced table a row in the series table is referring to. Be warned that doing this on a large table may cause a performance issue.
5 Replies
Hi DataNinja777
This is honestly better off done in M than in DAX. Your example, only has two rows so the latency is very very neglible but it isn't the case when you have thousands of rows not to mention the other calculations inside your model. But for the sake of whether it is possible in DAX, here's a sample calc table formula
Series = VAR __TBL = DATATABLE ( "Start Date", DATETIME, "End Date", DATETIME, "Index", INTEGER, { { "2024/7/20", "2029/7/19", 1 }, { "2023/1/1", "2024/12/31", 2 } } ) VAR __Series = GENERATESERIES ( 1, 60, 1 ) --must use the highest month count VAR __CROSSJOINED = ADDCOLUMNS ( CROSSJOIN ( __TBL, __Series ), "Date", EDATE ( [Start Date], [Value] ) - 1 ) -- this will generate dates way later than the end date RETURN FILTER ( __CROSSJOINED, [Date] <= [End Date] ) --filter the generated dates to include <= end dateHi danextian ,
Thank you for your input, however, the two lines are only simplified example, and I could have thousand lines for that, so typing hardcoded numbers is not an option for me. I was thinking something in line of the solution below by xifeng_L , but a much simpler scenario than the case below, as I don't need to make other inputs in the generateseries syntax dynamic.
Best regards,
This topic was solved by simplifying the code in the earlier topic as follows:
Series = GENERATE( VALUES('Tasks'[Task]), -- Iterate over distinct Task Names VAR MaxFinalLife = MAXX(FILTER('Tasks', 'Tasks'[Task] = EARLIER('Tasks'[Task])), 'Tasks'[Final Life]) RETURN SELECTCOLUMNS( GENERATESERIES(1, MaxFinalLife, 1), "Series", [Value] ) )