Using DAX to create a Calculated table

[simonchung]

Hi, 

I would like to create a table from A to B, expand to 12 months with Start Date, Amount distributes evenly to 12 months, is it possible? thanks so much!

Table A

ID	Start Date	Amount
A	Apr-24	12000
B	Aug-24	2400

Result: Table B

ID	Month	Amount
A	Apr-24	1000
A	May-24	1000
A	Jun-24	1000
A	Jul-24	1000
A	Aug-24	1000
A	Sep-24	1000
A	Oct-24	1000
A	Nov-24	1000
A	Dec-24	1000
A	Jan-25	1000
A	Feb-25	1000
A	Mar-25	1000
B	Aug-24	200
B	Sep-24	200
B	Oct-24	200
B	Nov-24	200
B	Dec-24	200
B	Jan-25	200
B	Feb-25	200
B	Mar-25	200
B	Apr-25	200
B	May-25	200
B	Jun-25	200
B	Jul-25	200

[DimaMD · Accepted solution]

@simonchung  Hi Try it DAX

ExpandedTable = 
VAR MonthsToExpand = 12
VAR T1 =
    GENERATE(
        'Table A',
        VAR StartDate = 'Table A'[Start Date]
        VAR AmountPerMonth = DIVIDE('Table A'[Amount], MonthsToExpand)
        VAR Dates = ADDCOLUMNS(
            GENERATESERIES(0, MonthsToExpand - 1, 1),
            "MonthDate", EDATE(StartDate, [Value])
        )
        RETURN
            SELECTCOLUMNS(
                Dates,
                "ID_Expanded", 'Table A'[ID],
                "Month", FORMAT([MonthDate], "MMM-yy"),
                "Amount1", AmountPerMonth
            )
    )
RETURN
SUMMARIZE( T1,[ID_Expanded],[Month],[Amount1])

[simonchung]

Exactly, thank you so so much!!