Working with Multiple Date columns in fact table

I have a single data model with a common calendar table and a fact table containing sales information and two date fields (Order date and Ship date). I have two reports - one gives basic sales information over time (eg: monthly sales totals, GM$, GM%), based on the Order date (the default relationship) and multiple associated visuals.

We want to have a second report with multiple calculations based on the Ship Date. I see from Sam’s video how to use a secondary relationship between the two tables by implementing a new measure for each calculation (UseRelationship) and that is clear.

My question: If we have (eg) 10 calculations which are common to each report but we need to utilise the respective date field in each report; is it best practice to just go ahead and write 10 new measures or is there another ‘Best Practice’ where I should consider doing something different with my model? (Duplicating the fact table and creating the relationship with the alternate date field - but that seems redundant). Or am I overthinking this and just need to get on with writing the new measures with the ‘UseRelationship’)?

Thanks in advance for any insight.

Answer from data mentor to learn more about what potentially you should do in the scenario

1 Like

Thanks Sam - looks like UseRelationship is the way forward in this scenario.