topic Date Table Hierarchy Issue in DAX Commands and Tips

Date Table Hierarchy Issue

rocketship — Sat, 03 Jul 2021 04:48:20 GMT

I'm having some date table issues in a new report I'm buiding. I'm seeing this icon I"m not familiar with and I'm not able to create a proper date Hierarchy.

When I create the Hierarchy, it looks like this

On a separate report i'm working with, this is the date table which works correctly for me.

The Dax for both master date tables is exactly the same:

Master Date = CALENDAR ( DATE ( 2020, 1, 1 ), TODAY() )

Wondering what's going on.

Thanks!

Re: Date Table Hierarchy Issue

Fowmy — Sat, 03 Jul 2021 06:57:27 GMT

rocketship

When you create a data table, always ensure the full year dates in your calendar table. in your case, you can see it up as with the additional columns then

Dates = CALENDAR( DATE( 2020, 1, 1) , DATE(2020, 12, 31) ) )

You need to enable the Auto Date Time under the Options as follows to get the date hierarchy for your date columns

Re: Date Table Hierarchy Issue

rocketship — Sat, 03 Jul 2021 13:26:38 GMT

Thanks for the quick responce. Unfortunatly Auto Date/Time was already selected for Time Intelligance in settings. Any other ideas?

Thanks,

Re: Date Table Hierarchy Issue

rocketship — Mon, 05 Jul 2021 18:36:22 GMT

The issue is related to the fact table and the formatting of the date from SQL. Because of an on prem dataset, live connection, I'm not able to use power query to fix. Had to go to database admin to fix.