Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Create a table to generate date by each column

Hello,

Need help dax formula or else.

My query that I wanted are, create a table to generate dates per each attribute.
From Month as start date to End Date

Denski11_0-1602490662251.png


Each attributes needs a start to end dates in a separate table.
Exmp.
RES 01/01/2020 475
RES 01/02/2020 475
...
..
RES 01/31/2020 475
ENTERPRIZE ELITE 01/01/2020 1022
ENTERPRIZE ELITE 01/02/2020 1022
..
..
ENTERPRIZE ELITE 01/31/2020 1022

Big help for me.

4 REPLIES 4
CNENFRNL
Community Champion
Community Champion

Hi, @Anonymous , I prefer Power Query to DAX in terms of such data transformation. You may want to follow this pattern.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtA1MAQiJR2lINdgBVcPFyDLxNwUSMIkjQ2VYnXQ1LrmlaQWFRRlFqcquOZklqQChQwNjIzQdMUCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StartDate = _t, Attribute = _t, Value = _t, EndDate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"StartDate", type date}, {"Attribute", type text}, {"Value", Int64.Type}, {"EndDate", type date}}),
    
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Dates([StartDate], Duration.Days([EndDate]-[StartDate])+1, #duration(1,0,0,0))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")
in
    #"Expanded Custom"

Screenshot 2020-10-12 111351.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Anonymous
Not applicable

Denski11_0-1602499700168.png

@CNENFRNL 

Thanks for your code, however I just copy paste your codes.
And I'm having an error on "Added Custom". I dont know if I'm getting the right list.date

THanks for having this

Hi, @Anonymous , there are some glithes in your code; pls change them like this,

#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Dates([StartDate], Duration.Days(Date.EndOfMonth([StartDate])-[StartDate])+1, #duration(1,0,0,0))),

Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Anonymous
Not applicable

Can I used that your query? may I know how I can edit the table you created?.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.