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
Jeanxyz
Post Prodigy
Post Prodigy

how to expand a table in DAX

I have created a table in DAX as below: 

'test = summarize(filter('Fact_GL Entries','Fact_GL Entries'[Item start]>0), 'Fact_GL Entries'[Company Currency],'Fact_GL Entries'[Company Currency Amount],'Fact_GL Entries'[Item start],'Fact_GL Entries'[Item End])
 
The output will look like this. 
Company CurrencyCompany Currency AmountItem startItem End
EUR3192821/1/202312/31/2023

 

I want to expland each line of data into multiple lines based on the Item Start month and Item End month, so the first line of data should become 12 lines because the item start in 2023 Jan and ends in 2023 Dec. I would also like to restore the year month information in a new field called [Date Key] . How can I do this in DAX?

Company CurrencyCompany Currency AmountItem startItem EndDate Key
EUR3192821/1/202312/31/20231/1/2023
EUR3192821/1/202312/31/20232/1/2023
EUR3192821/1/202312/31/20233/1/2023
EUR3192821/1/202312/31/20234/1/2023
EUR3192821/1/202312/31/20235/1/2023
EUR3192821/1/202312/31/20236/1/2023
EUR3192821/1/202312/31/20237/1/2023
EUR3192821/1/202312/31/20238/1/2023
EUR3192821/1/202312/31/20239/1/2023
EUR3192821/1/202312/31/202310/1/2023
EUR3192821/1/202312/31/202311/1/2023
EUR3192821/1/202312/31/202312/1/2023
1 ACCEPTED SOLUTION
Dangar332
Super User
Super User

hi, @Jeanxyz 

 

make a new table of date with start of every month

like below

Dangar332_0-1710242895312.png

 

then use below code 

just adjust colum name and table name

 

result table = 
GENERATE(
    'Table',
    FILTER(
        'firstdate',
        MONTH('firstdate'[date])>=MONTH('Table'[Item start])
         && MONTH('firstdate'[date])<=MONTH('Table'[Item End])
    )
)

 

 

output like below

Dangar332_1-1710243141239.png

 

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

View solution in original post

1 REPLY 1
Dangar332
Super User
Super User

hi, @Jeanxyz 

 

make a new table of date with start of every month

like below

Dangar332_0-1710242895312.png

 

then use below code 

just adjust colum name and table name

 

result table = 
GENERATE(
    'Table',
    FILTER(
        'firstdate',
        MONTH('firstdate'[date])>=MONTH('Table'[Item start])
         && MONTH('firstdate'[date])<=MONTH('Table'[Item End])
    )
)

 

 

output like below

Dangar332_1-1710243141239.png

 

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 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.

Top Solution Authors