Forum Discussion

Jolyon's avatar
Jolyon
Icon for Helper III rankHelper III
9 years ago

how to sort Date (MonthYear column)?

Hello, need urgent help of yours:   I have a column Date (e.g. 01.01.2016), then I added columns Month and Year and concatenated them to get the column MonthYear(e.g. January2016):     No...
Need Help
tringuyenminh92's avatar
tringuyenminh92
Icon for Memorable Member rankMemorable Member
9 years ago

Hi Jolyon,

 

In case you want to sort by Year and Month of date, you could add Calcuated column for YearMonthInt as:

YearMonthInt = Dates[Date].[Year]*100 +  Dates[Date].[MonthNo]

Then choose MonthYear column and choose sort by YearMonthInt column.

 

 

If this works for you please accept it as solution and also like to give KUDOS.

Best regards
Tri Nguyen

Jolyon's avatar
Jolyon
Icon for Helper III rankHelper III
9 years ago

hi tringuyenminh92
could you please also tell, how you concatenated MonthYear?

Question to this formula:   YearMonthInt = Dates[Date].[Year]*100 +  Dates[Date].[MonthNo]  : is Dates the name of table? "Year" and "MonthNo" are your columns or DAX functions?

because in my case PowerBI underlines this formula as red: YearMonthInt = Client[Datum].[Jahr]*100 +  Client[Datum].[Mon]

 

thank you kindly!

  • tringuyenminh92's avatar
    tringuyenminh92
    Icon for Memorable Member rankMemorable Member
    9 years ago

    When we cast Date column to Date type, PBI will suport us to access its Month (month name) and Month value (MonthNo)

    My MonthYear= Dates[Date].[Month] & "-" & Dates[Date].[Year]

    I think your expression of YearMonthInt is correct, just choose sort by column.

     

    • Jolyon's avatar
      Jolyon
      Icon for Helper III rankHelper III
      9 years ago

      tringuyenminh92
      hm, it is a bit strange, because I get error message from Power BI, that says : the variation column Month and Year couldn't be found in the table:

       

      Table1 is name of my table; Date is name of my Date column; I have Date in format dd.mm.yyyy.
      I tried both German and English in the formula for the Month and Year, but it doesn't work.

      And the same problem with YearMonthInt = Dates[Date].[Year]*100 +  Dates[Date].[MonthNo]  --> in my report I get error message.

       

      Is DATES in your report the name of your table?

      thanks for the help!

      • tringuyenminh92's avatar
        tringuyenminh92
        Icon for Memorable Member rankMemorable Member
        9 years ago

        Hi Jolyon,

         

        yes, Dates is table name. Please ensure your data type of Date column is Date cause as my observation, i dont think your Date column is in Date type. So it doesnt support additional fields.

        If you got problems with casting Date column, you could substring by left or Right method for your Date column and use Value() method to convert text to int.