custom data hierarchy issue

[kobe]

I create a custom date hierarchy in order to have more selections such as weeknum and weekday. The conlumn of month (Januray, Feburary...) and weekday (Monday Tuesday...) are in Text format. They could not be transformed into date or time format. This cause my chart looks differently comparing using auto-generated date hierarchy.

I understand there is a way to sort the order properly by adding index conlums but then I have to add several conlumns to the master table. So I was wondering if there is a better way to do it. 

Besides, auto-generated date hierarchy disappears after I create the custom one. I also tried to add separate date table, link and unlink it to the master table. Not sure which step made it gone away.  How to get it back so I can use both at the same time?

[ToddChitt]

In your Date table, add columns for WeekDay (INT, 1 - 7) and MONTH (1-12). Then for the Week day name and month name columns, use the Sort By option (on the Column Tools menu) to sort those column by their respective integer columns.

In your visual, if you sort the X axis by Month Name, it will use the 1, 2, 3, etc. values from the Month Number column

[kobe]

Thanks for your fast response.

I actually create the custom date hierarchy inside my master table as the separate date table isn't working for some reasons. It seems I have no other options. 

[ToddChitt]

Seriously, you should have a Date Dimension table in your model. Let's start there. Use this DAX statement to get started with a new table.

MyDates = CALENDAR("01/01/2020", "12/31/2024")

Then join that to your 'master data table' on the dates