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.
G.