Forum Discussion

Sylvain74's avatar
Sylvain74
Helper III
4 years ago
Solved

Dealing with dates

Hello,

 

Let's say in my data model I have 1 or more tables with multiple datecode columns (yyyymmdd). I have as well one single dim_time table.

Should I add the dim_time table as many time as I have datecode columns or should I add it only once and use "Relationship" dax instruction?

Should I create multiple calculated table based on dim_time?

What is the best practices?

I don't have any calculation based on that dates, usually I use the dim_time table just to display the date in a user friendly fashion and sometime in a slicer as well (e.g. order_date to see only purchase order in period)

 

Thanks 

Sylvain

Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    4 years ago

    Hi  Sylvain74 ,

     

    Yes,I think creating sereval dim_time tables would make your model look more clean and convenient.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my reply as a solution!

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Sylvain74 , Hope you are checking about date and calendar table. In your calendar table you can have date and datekey

     

    Datekey = format([Date], "YYYYMMDD")

     

    and you can join on this or date column

    • Sylvain74's avatar
      Sylvain74
      Helper III
      4 years ago

      amitchandak : I already have the datekey in the dim_time table. The question is not here...

      Actually I am wondering what is the good practice when you have multiple date columns in your tables. Should I add multiple dim_time tables and link them respectively to each date columns?

      • HotChilli's avatar
        HotChilli
        Community Champion
        4 years ago

        There is no best practice here. It totally depends on what the visualisations/dashboard is doing.

         

        A relationship means filtering so you might want to 2 dimensions (each in a slicer)  to filter a fact table on, say, Txn Date and Ship Date without the 2 dates filtering each other (if that makes sense)

        You can read this for an introduction radacad/role-playing