Unable to create Date Hierarchy

[ArchStanton]

Hi,

I have a large data in my data moidel that has dozens of date fields.

Some of them have Date Hierarchies and others do not, I wlould like the applicationvalidationdate to have one like the Authority Rec Date has below. How do I do this? The applicationvalidationdate was formatted as Date/Time in Power Query, I've now updated that to Date like all the others.

Thanks

[jdbuchanan71 · Accepted solution]

You could try turning auto date time off then back on.  That might triggger the creation of the date heriarcy on the field.  Please save a backup of your file before making any changes.

File > Options and Settings > Options

Current File > Data Load > Time intelligence

Un-check Auto date/time and click ok then go back in and turn it back on.

[timalbers · Accepted solution]

HI @ArchStanton 

do you have a calendar table in your model, which has a relationship to the "applicationvalidationdate" field?

When there's a relationship to a calendar table, the date hierarchy of the field in the fact table gets removed automatically. You can use the date hierarchy of the date field in your calendar table instead.

Cheers

Tim

[ArchStanton]

Thank you, I added an inactive relationship earlier to see what would happen because there was no hierarchy available before - I first toggled the auto Time Intelligence setting then I removed the inactive relationship and it worked. Not sure which reply fixed it - probably a joint effort! 😊

[jdbuchanan71 · Accepted solution]

You could try turning auto date time off then back on.  That might triggger the creation of the date heriarcy on the field.  Please save a backup of your file before making any changes.

File > Options and Settings > Options

Current File > Data Load > Time intelligence

Un-check Auto date/time and click ok then go back in and turn it back on.

[ArchStanton]

Thank you, I  toggled the auto Time Intelligence setting then I removed the inactive relationship and it worked. Not sure which reply fixed it - probably a joint effort! 👍