How to structure database with multiple dates per contract

[CloudMonkey]

Hi All,

For each contract I have a several dates: contract creation date, initial client meeting date, service date and signoff date.

Please could you tell me how I should manage these dates? should i create one linked calendar table per date, and then an extra master calendar table that they all link to for when I need to plot measures based on different dates on the same graph? (if there should be a master calendar table should it link to the other calendar tables, or directly to the contract table's date fields?)

Please could you tell me which relationships should be active and inactive? Is there a best practice?

Thank you for your help!

CM

[Anonymous]

Hi @CloudMonkey ,

You can create multiple relationships between your contract table and calendar table, the field contract creation date, initial client meeting date, service date and signoff date of contract table can be link to date field of calendar table. But only one relationship can be set as Active. You can refer the following blogs to get it.

Role Playing Dimensions

How To Work With Multiple Dates In Power BI

Best Regards

[amitchandak]

@CloudMonkey , if you need to filter them on the same date/date range. You can join them on one date table and manage inactive using userelation.

But if you need a different duration on creation date, initial client meeting date, service date, then you need more date table or you can use these dates

refer

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[CloudMonkey]

Thank you @amitchandak please could you tell me what you mean by "different duration"?

Many thanks

[amitchandak]

@CloudMonkey , Creation date in Jan-2021 and initial client meeting date in Feb 2021.

Both filter at the same time.