Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Date Slider Not Handling Years Correctly

I'm making a report where every visual is filtered by a date slider at the top of the page. It used to function properly, but now it will disregard the year and order the months in chronological ord...
Need Help
parry2k's avatar
parry2k
Super User
7 years ago

Anonymous  by default it sorting alphabatically. You need add another column in your table to sort this month field.

 

Add following column in your date dimenson, change table name and column name as per your model.

 

Month Sort = FORMAT( Table[Date], "YYYY-MM" )

Now go to modelling tab, choose your month colum and click sort by column on the menu and select "Month Sort". This will fix the issue.

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Thanks! This seems to be on the right path. When I try to sort on "Month Sort" I get the error "This column can't be sorted by a column that is already sorted, directly or indirectly, by this column."

  • parry2k's avatar
    parry2k
    Super User
    7 years ago

    Anonymous do you have date dimension in your model? The month column you are trying to sort, are you using the same column to create sort column?

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      I have multiple "Milestone" columns for various completions, but no specfic date column (I'm pulling all of my data from an API).

      I changed the "Month Sort" column to be based off of another milestone than the column I'm trying to sort and now get this error:
      "We can't sort the "Sorting Column" by 'Month Sort'. There can't be more than one value in 'Month Sort' for the same value in "Sorting Coumn". Please choose a different column for sorting or update the data in 'Month Sort'.

      • parry2k's avatar
        parry2k
        Super User
        7 years ago

        Anonymous  I know what this error message is, having said that I would highly recommend to add date/calendar dimension in your model and use that to do anything related to time intelligence.

         

        There are many topics on how to add date/calendar table in your model, you can use power query or dax to create the table. It is easier way to work on dates and also it is best practice.