Date Slider Not Handling Years Correctly

[Anonymous]

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 order.

 Exmaple: The slider is a date range from November 2018 - March 2019.

The charts are listed in the following order:
January 2019

February 2019

March 2019

November 2018

December 2018

[parry2k]

@Anonymous do you have sort order on chat to z->a instead of a->z, click on dots on top right corner of the chart and check the sort order.

[Anonymous]

Regardless of A>Z vs. Z>A, it displays incorrectly.

A>Z it appears as follows:
January 2019

February 2019

March 2019

November 2018

December 2018

Z>A it appears as follows:
December 2018

November 2018

March 2019

February 2019

January 2019

Power BI is not correctly recognizing the year.

[parry2k]

@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]

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]

@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]

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]

@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.

[Anonymous]

@parry2kThanks! I created a date table following this set of instructions.

From here, what is the best practice/ what do you recommend?
Should I still create a "Month Sort" column based off of my new date column?

Shouhld I just manage the relationships and then use the date column from the new date table in my visuals?

Just want to make sure I'm setting everything up in the best way 🙂

[parry2k]

@Anonymous beautiful, you are on right track.

Now set the relationship between your new date table and your transaction table. Do month sort in your date table and use month from date table in your visuals.

Assume you don't have date fields in your transaction table (just hide those) and anything related to time intelligence use fields from date table.

Good luck!!

[Anonymous]

@parry2kApologies! I am getting some funky results -- this is probably intuitive for most, but just to clarify, here's what I did:

-The "Month Sort" column is in the transaction table and it is based on my "Milestone Date" column from the transaction table

-I linked the transaction table and date table in relationships. I connected the "Milestone Date" in my transaction table to my "Date" column in the date table.

-On my slicer for my visual, I put the "Date" column from my date table into the field.

Please let me know where I went wrong and what do differently. My end goal is to have a slider that correctly filters all of my visuals by the "Milestone Date".

Thank you for your patience!

[parry2k]

@Anonymous  it is too hard to explain everything, if you are ok PM me directly and we can work on it.

[Anonymous]

@parry2k  I don't think it will allow me to PM you.... unless I'm missing some obvious functionality.