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?

Sean's avatar
Sean
Community Champion
9 years ago

tylerdv

The easiest way would be to reference your Date Column like this :smileyhappy:

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

 

tylerdv's avatar
tylerdv
Frequent Visitor
9 years ago

Hi Sean

 

Thanks for the response, when doing that the values come out as decimal numbers 

 

42614

62644

62675 etc

When formating them to date it includes the dd-mm-yyyy again

 

  • 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?