Best practice datamodel -> tables with start and end date in one row

[Anonymous]

Hello, 

I am working on a new project. 3 tables has a start date and an end date in the rows

Table 1 -> start contract  and end contract

Table 2 -> start function and end function

Table 3 -> start project and end project

What is the best practice to setup the data model and relations in power BI. The goal is to make dashboards to combine these data in one table and see for example at the end of september the results.

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

In your scenario, we can create measure to work on it instead of establishing relationship between tables. 

1. New a calendar table as below.

date = CALENDARAUTO()

2. Create a measure and add it to the table visual.

Measure = 
VAR mind =
    MIN ( 'date'[Date] )
VAR maxd =
    MAX ( 'date'[Date] )
VAR sdate =
    MAX ( 'Table'[S-date] )
VAR edate =
    MAX ( 'Table'[E-date] )
RETURN
    IF ( sdate <= mind && edate >= maxd, "in", BLANK () )

[Anonymous]

Thank you!