Skip to main content
cancel
Showing results for 
Search instead 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

Reply
simonchung
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

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
1 ACCEPTED SOLUTION
DimaMD
Solution Sage
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)
        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])

__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com

View solution in original post

2 REPLIES 2
DimaMD
Solution Sage
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)
        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])

__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com

Exactly, thank you so so much!!

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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