cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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 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
1 ACCEPTED SOLUTION
Solution Sage

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

__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com
2 REPLIES 2
Solution Sage

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

__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com
Frequent Visitor

Exactly, thank you so so much!!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors