Thanks @v-chuncz-msft for the help, the approach with GENERATE() plus some additional code allowed me to create the table I needed. Of course the real problem is a bit more complicated than the simplified example I used in the questions, so this is how I used GENERATE to solve the issue:
My end goal was to estimate future states of our contracts and create a time series of accrued contract revenue from this estimate. Summarizing this table provides the accounted (booked) revenue in accordance to the accounting principles used in our firm.
Starting tables:
Contract Headers
| ContractId | Period Start | Period End | Expires On | Period Length (Months) |
| 1 | 01.01.2019 | 31.12.2019 | null | 12 |
| 2 | 15.02.2019 | 14.02.2020 | 14.08.2020 | 12 |
| 3 | 15.03.2019 | 15.09.2019 | null | 6 |
| 4 | 01.06.2019 | 01.12.2019 | null | 6 |
| 5 | 12.08.2019 | 12.02.2020 | 12.10.2020 | 6 |
| 6 | 15.08.2019 | 15.02.2020 | null | 6 |
Contract Lines
| ContractDetailId | Period Value |
| 1-1 | 50 |
| 1-2 | 25 |
| 1-3 | 25 |
| 2-1 | 50 |
| 2-2 | 50 |
| ... | ... |
Step 1: Creating the base table
In the query I merged the Contract Headers and Contract Lines in order to get one table with all contract lines with contract periods. Calculated value per day from the contract periods.
Step 2: Calculate number of future renewals of the contracts
Calculated the number of contract renewals each contract can have in 6 calendar years. Then generated a series of renewal rounds from 0 (current contract state) to the maximum number of renewals for the shortest contract period (monthly contracts).
GENERATESERIES(
0;
72;
1
)
Step 3: Generate a new table crossjoining each row from Step 1 with each row from Step 2
GENERATE(
Output Table from Step1;
Output Table from Step2
)
This code gave me a new table with where each row in the contract table was crossjoined with each of the round numbers from Step 2. The table contained appr. 1,3 million rows.
Step 4: Calculating the future dates of renewals per contract per renewal round
I used SELECTCOLUMN() to create a new table from the output of Step 3 and to add two calculated columns calculating Period Start and Period End per renewal round.
SELECTCOLUMNS(
Output table from Step 3;
...
// added all the existing columns from the tables
...
"Current Round Period Start";
EDATE(
[Period Start];
[Period Length (Months)] * [Renewal Round Number]
);
"Current Round Period End";
EDATE(
[Period End];
[Period Length (Months)] * [Renewal Round Number]
)
)
Step 5: Generate a new table crossjoining the output table from Step 3 with a calendar table generated in-memory
This is the step directly using the DAX code from https://community.powerbi.com/t5/Developer/Dax-Help-Create-table-with-variable-of-lines-according-to...
VAR calendar = CALENDARAUTO()
Return
GENERATE(
Output Table from Step 4;
FILTER(
calendar;
[Date] >= [Current Round Period Start] && [Date] <= [Current Round Period End]
)
)End result:
a table with a time series of values and periods from Today and 5 years in the future (30M+ rows)
