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

IDStart DateAmount
AApr-2412000
BAug-242400


Result: Table B

IDMonthAmount
AApr-241000
AMay-241000
AJun-241000
AJul-241000
AAug-241000
ASep-241000
AOct-241000
ANov-241000
ADec-241000
AJan-251000
AFeb-251000
AMar-251000
BAug-24200
BSep-24200
BOct-24200
BNov-24200
BDec-24200
BJan-25200
BFeb-25200
BMar-25200
BApr-25200
BMay-25200
BJun-25200
BJul-25200
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])

2 Replies

  • 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])