Forum Discussion

rima_ch's avatar
rima_ch
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  do 

  • Anonymous's avatar
    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

8 Replies

  • Nathaniel_C's avatar
    Nathaniel_C
    Community Champion

    Hi rima_ch ,
    Go to power query and paste this into the advanced editor:

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTSN9Q3MjC0VNIBsY1BHCMTpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Start = _t, End = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start", type date}, {"End", type date}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type", "Dates", each {Number.From([Start])..Number.From([End])}),
        #"Expanded Dates" = Table.ExpandListColumn(#"Added Custom", "Dates"),
        #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Dates",{{"Dates", type date}}),
        #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Year", each Date.Year([Dates])),
        #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Month", each Date.MonthName([Dates])),
        #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom2",{{"Year", type text}, {"Month", type text}}),
        #"Removed Other Columns" = Table.SelectColumns(#"Changed Type2",{"Year", "Month"}),
        #"Grouped Rows" = Table.Group(#"Removed Other Columns", {"Year", "Month"}, {{"det", each _, type table [Year=nullable text, Month=nullable text]}}),
        #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"det"})
    in
        #"Removed Columns"


    Let me know if you have any questions.

    If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
    Nathaniel

    • Nathaniel_C's avatar
      Nathaniel_C
      Community Champion

      Hi rima_ch ,
      Here are pics of steps in reverse order.  
      Let me know if you have any questions.

      If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
      Nathaniel
      Advanced Editor paste codeHome tab in PQ new source blank queryIn PowerBI Transform Data on home tabFinal table

      • rima_ch's avatar
        rima_ch
        Frequent Visitor

        Thank you very much this is so helpful but could you please tell me how to create it Dax  and not power query

  • 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's avatar
      rima_ch
      Frequent Visitor

      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's avatar
        AlexisOlson
        Super User

        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" )
        )

         

  • Anonymous's avatar
    Anonymous
    Not applicable

    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