Forum Discussion

simonchung's avatar
simonchung
Frequent Visitor
2 years ago
Solved

Using DAX to create a Calculated table

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 ...
Dax Help
  • DimaMD's avatar
    DimaMD
    2 years ago

    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])
DimaMD's avatar
DimaMD
Solution Sage
2 years ago

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's avatar
    simonchung
    Frequent Visitor
    2 years ago

    Exactly, thank you so so much!!