SQL DATE Hierarchy

[Anonymous]

I understand that pulling in dates from SQL messes with the date hierarchy, so I imported my date table (calendarlookup) because we have a funky Fiscal Year and created a date hierarchy using that date table using these formulas:

DateYear = YEAR('calendarlookup'[DATE])
DateMonth = MONTH('calendarlookup'[DATE])
DateDay = DAY('calendarlookup'[DATE])

I'm using a Slicer / List with the Month and Day as the fields as the visuals will be the totals for each Year and this way they can choose the specific months that they want to compare across those years.

I wanted to use the Name of the month, so I tried 
DateMonthName = FORMAT('calendarlookup'[date], "MMM") - but that sorts alphabetically rather than by the date.  

I did the "sort by column" option and chose the unique date field, but that didn't help either.

Any help on getting the list to look like this (which worked when I brought in the data through Excel)

Instead of this (date brought in through SQL server)

would be greatly appreciated!

Thanks much!

Dawn

[v-stephen-msft · Accepted solution]

Hi @Anonymous ,

You can try to sort Month column by MonthNum column.

Here's the example.

MonthNum = MONTH([Date])

Month = FORMAT([Date],"mmm")

And hierarchy worked.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AllisonKennedy]

@Anonymous  You should do all of this in the date table, and your sort by column must be on a month number, not unique date. Ie if you have 12 Month names you must sort by a column that has 12 month numbers (the Date column has 365 unique values in a year instead of 12) 

Here's a M script for a date table if you don't have one already; 

https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html 

[Anonymous]

Thank you for the info!  That works on my other reports that don't pull in from SQL but not on the SQL ones.  The field is formatted in SQL as date/time and I change it to date in the Query Editor once it's been imported. 

I have a date table that has our very weird FY dates that I use with other reports that works well, but - again - just can't make it do it's thing here.  Once I link the date table to the SQL imported table, it won't give me the hierarchy, so I created the hierarchy manually thinking that might help, but it didn't.