Trying to figure out Date table

[BooDaa]

Hi guys!
I have come to understand that a universal date table is a pre requisite if I want to compare more than one date per row in a visualization (start/end, open/close, hire/termination and so on). My main source of inspiration this far is @amitchandaks article https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Problem:
My main set of records where created during current or previous two calendar years, so I am used to a slicer looking like this:

However, when I create a relation between the two table my hierarchy break and I am left with individual dates.

If i try to use the hierarchy from the date table my slicer span from 2021 to 2121, and I can't quite figure out why.

Questions:
- Is there a way to create a relation between my main table and date table without breaking the hierarchy?
- Should I use the date table for my slicer and filter it by dates available in my main table?

Best regards,
Fredrik

[amitchandak]

@BooDaa , No. The join column will not have a hierarchy. Also, Date of date column used as date(when you mark as a date table) will not have a hierarchy

You can use date keys to join (so date retain hierarchy)

Datekey = year([Date])*10000 + month([Date]) *100 + day([date])

or duplicate the date

Date1 =[Date]

The new dates or some other dates can have a hierarchy.

I personally do not use default date hierarchy, it can break time intelligence

I create columns in date table and use those