Question about date table management

Hi josborne 

At high level, I probably would do this:

Table 1:

sw_assignmentdate --> Date [active]

sw_closedate --> Date [inactive]

Table 2: 

sw_effortweekdate --> Date [active]

Table 3: 

date bid team received --> Date [active]

You can get rid of the rest. Rely on measures and calculations to return you the correct values.

I would probably also make some additional changes to your model. Seems you have columns that are not necessary. For example, I would probably move / merge the transactional data from Win Rate Tracker to Pa_Tbl_Main and re-purpose the Win Rate Tracker table as a dimension table.

Hi josborne 

Can you implement the data model with these relationships and confirm if the calculations are working
Ensure to have only one Date table

Table 1 – Project
Active relation|| Date[Date] to Table1[Assignment Date]

Inactive relation || Date[Date] to Table1[Closed Date]

Table 2 – Effort

Active relation || Date[Date] to Table2[EffortWeek Date]

Table 3 – Bid Tracker
Active relation || Date[Date] to Table3[Date BidTeamReceived]

Inactive relation || Date[Date] → Table3[BidOutcome Date]

You can bring Table 1a columns into Table1 based on ProjectID column. Also Table 3a columns into Table3 based on BidID column. Please ensure to have only required columns for reporting in Table1 and Table3.

1) Stop duplicating the Date table

Delete Date table #5. You only need one calendar (your FinYearCalendar).

If you need multiple date roles, you handle that with inactive relationships and measure using USERELATIONSHIP(), not multiple calendars.

2) Don’t create “subset fact copies” (1a, 3a) just for outcome dates

Again you do no thave to replicate Fact tables as it would increate your memory usage, thus you might have performance issiues as data gets larger. Instead consider using inactive relations & USERELATIONSHIP() for those facts with different date keys.

3) If you need to switch the “date role” dynamically

If your users need a slicer like “Use Assignment Date vs Closed Date”, the best practice is a Calculation Group to apply the right USERELATIONSHIP automatically across measures.