Forum Discussion

BooDaa's avatar
BooDaa
Frequent Visitor
3 years ago

Trying to figure out Date table

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-trend/ba-p/882970#

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

Need Help

1 Reply

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    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