topic Using DAX to create a Calculated table in DAX Commands and Tips

Using DAX to create a Calculated table

simonchung — Sat, 25 May 2024 04:20:34 GMT

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

Re: Using DAX to create a Calculated table

DimaMD — Fri, 24 May 2024 12:58:35 GMT

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

Re: Using DAX to create a Calculated table

simonchung — Sat, 25 May 2024 03:39:23 GMT

Exactly, thank you so so much!!