Forum Discussion

tylerdv's avatar
tylerdv
Frequent Visitor
9 years ago
Solved

Create YYYY-MM column

Hi Guys   I am trying to create my monthly reporting where i can show the results for the last 12 months (eg April 16 to April 17   I am having issues sorting the data in chronological order.   ...
Need Help
  • Sean's avatar
    Sean
    9 years ago

    tylerdv

    My suggestion above was to create a new DAX Column referencing your Date column

    That would be on the Modeling Tab - New Column (NOT in the Query Editor)

    YYYY-MM Column = VALUE ( FORMAT ( CalendarTable[Date], "YYYYMM" ) )

    If you want this done with M in the Query Editor - Add Column tab - Custom Column

    and use the columns Month and Year (which I assume you added using the From Date & Time option on the Add Column tab)

    = Number.ToText([Year]) & (if [Month] < 10 then "0" else "") & Number.ToText([Month])

    or if you want to refence the Date column again

    = Number.ToText(Date.Year([Date])) & (if(Date.Month([Date])) < 10 then "0" else "") & Number.ToText(Date.Month([Date]))

    there may be an easier way with M (like there is in DAX using FORMAT and VALUE with no IF statement)

    Perhaps MarcelBeug can tell us?

     

    However that should do it! :smileyhappy:

     

    If not post a screenshot of what goes wrong and where?

Anonymous's avatar
Anonymous
Not applicable
9 years ago

tylerdvno he means you should create a new column using that formula. Then you can use that column as a sort value for your month column using the Sort By Other Column button.

tylerdv's avatar
tylerdv
Frequent Visitor
9 years ago

Is there a link shwoing me how to do this properly? 

 

Can work it out sorry.

  • Sean's avatar
    Sean
    Community Champion
    9 years ago

    tylerdv

    My suggestion above was to create a new DAX Column referencing your Date column

    That would be on the Modeling Tab - New Column (NOT in the Query Editor)

    YYYY-MM Column = VALUE ( FORMAT ( CalendarTable[Date], "YYYYMM" ) )

    If you want this done with M in the Query Editor - Add Column tab - Custom Column

    and use the columns Month and Year (which I assume you added using the From Date & Time option on the Add Column tab)

    = Number.ToText([Year]) & (if [Month] < 10 then "0" else "") & Number.ToText([Month])

    or if you want to refence the Date column again

    = Number.ToText(Date.Year([Date])) & (if(Date.Month([Date])) < 10 then "0" else "") & Number.ToText(Date.Month([Date]))

    there may be an easier way with M (like there is in DAX using FORMAT and VALUE with no IF statement)

    Perhaps MarcelBeug can tell us?

     

    However that should do it! :smileyhappy:

     

    If not post a screenshot of what goes wrong and where?

    • tylerdv's avatar
      tylerdv
      Frequent Visitor
      9 years ago

      Sean

       

      Query Editor custom column works a treat!


      Thanks heaps

       

      • MarcelBeug's avatar
        MarcelBeug
        Community Champion
        9 years ago

        Sean, tylerdv

         

        My suggestion would be

         

        = Text.From(100*[Year]+[Month])

        or

         

        = Text.From(100*Date.Year([Date])+Date.Month([Date]))

         

        Alternatively you can omit the Text.From. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      I know this is an old Post but hopefully someone might find this useful. 

       

      Using M in Power Query you can create the format YYYYMM by using the following

       

      Table.AddColumn(#"Renamed Columns1", "Period Key", each Date.ToText([FullDate],"yyyyMM"))

       

      This will add a 0 in front of the months 1-9 automatically. [Full Date] is just a date column