Forum Discussion

Frequent Visitor

4 years ago

Solved

undefined

Hello everyone, I want to create a table that has only the year and month between 2 dates 2019-12-1 2024-12-31 my table needs to be like this could someone please tell me what should I...

Need Help

Anonymous

4 years ago

Hi rima_ch ,

Check the new table formula.

Table = distinct(SELECTCOLUMNS(CALENDAR(DATE(2019,12,1),DATE(2024,12,31)),"Year",YEAR([Date]),"Month",FORMAT([Date],"MMMM")))

Best Regards,

Jay

AlexisOlson

Super User

4 years ago

Another possible construction:

let
    Start = #date(2019,12,1),
    End = #date(2024,12,31),
    Years = {Date.Year(Start)..Date.Year(End)},
    #"Converted to Table" = Table.FromList(Years, Splitter.SplitByNothing(), {"Year"}),
    #"Added Months" = Table.AddColumn(#"Converted to Table", "Month", each {1..12}),
    #"Expanded Month" = Table.ExpandListColumn(#"Added Months", "Month"),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Month",{{"Year", Int64.Type}, {"Month", Int64.Type}}),
    #"Added Date" = Table.AddColumn(#"Changed Type", "Date", each #date([Year], [Month], 1), type date),
    #"Filtered Rows" = Table.SelectRows(#"Added Date", each [Date] >= Start and [Date] <= End),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "MonthName", each Date.MonthName([Date]), type text)
in
    #"Added Custom"

You can keep or delete the extra columns as you wish.

rima_ch

Frequent Visitor

4 years ago

Thank you a lot this is what I need but could you please tell me how to create it Dax and not power query

AlexisOlson

Super User

4 years ago

DAX is a bit simpler in this case:

MonthlyCalendar =
ADDCOLUMNS (
    FILTER (
        CALENDAR (
			DATE ( 2019, 12, 1 ),
			DATE ( 2024, 12, 31 )
		),
        DAY ( [Date] ) = 1
    ),
    "Year", YEAR ( [Date] ),
    "Month", MONTH ( [Date] ),
    "MonthName", FORMAT ( [Date], "mmmm" )
)