How to create dynamic child table with DAX in PowerBI

Question

I have this table:

Order | Total  | FirstPayment | Months
1     | 1000   | 2021-01-01   | 2
2     | 600    | 2021-02-01   | 3

And I need to create a another table with the installments, like this:

Month      | Order  | Value
2021-01-01 | 1      | 500
2021-02-01 | 1      | 500
2021-02-01 | 2      | 200
2021-03-01 | 2      | 200
2021-04-01 | 2      | 200

So, I want to create a child table with one row for each month of payment.

Please, can you help?

Answer 1

As per my comment, I would actually do it like this:

Create dates table that spans all dates between two ranges. You could actually filter it to contain only relevant dates for better performance, but I didn't bother (this is a table formula):

Payments = CALENDAR(MIN(Orders[FirstPayment]), MAXX(Orders, EDATE(Orders[FirstPayment], Orders[Months])))

Create a measure that would show appropriate values for relevant dates:

Payment amount = 
SUMX (
    Payments,
    VAR d =
        DAY ( Payments[Date] )
    RETURN
        SUMX (
            FILTER (
                Orders,
                DAY ( Orders[FirstPayment] )
                    == d
                        && Payments[Date] <= EDATE ( Orders[FirstPayment], Orders[Months] -1 )
                        && Payments[Date] >= Orders[FirstPayment]
            ),
            [Total] / [Months]
        )
)

The result - based on Order from Orders table and Date from Payments table:

EDIT

Of course, it is also possible to do what you asked. You have to combine the two formulas to create a calculated table like this (below is a table formula that you apply when you select New table):

Installments =
SELECTCOLUMNS (
    FILTER (
        CROSSJOIN (
            CALENDAR (
                MIN ( Orders[FirstPayment] ),
                MAXX ( Orders, EDATE ( Orders[FirstPayment], Orders[Months] ) )
            ),
            Orders
        ),
        [Date] >= [FirstPayment]
            && DAY ( [Date] ) = DAY ( [FirstPayment] )
            && [Date]
                <= EDATE ( [FirstPayment], [Months] - 1 )
    ),
    "Date", [Date],
    "Order", [Order],
    "Value", [Total] / [Months]
)