Forum Discussion
DataNinja777
2 years agoSuper User
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 [,...
- 2 years ago
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] ) ) - 2 years ago
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.
danextian
2 years agoSuper User
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.
DataNinja777
2 years agoSuper User
Thank you danextian . It works perfectly.