Hi All,
Trust you are keeping well. Just wanted to get some ideas and perspective on the best way to approach a model that I’m currently working on.
I am receiving data from a source system that comes in the format below:
I will be looking to hopefully carry out time intelligence calculations etc.
Would love to hear about some possible approaches ( have a couple in mind) but would love to know if there is a particular best practice for such a scenario.
Thanks in advance,
Haroon
This is nice clean data, and they were generous enough to already calculate duration for you so you’re off to a good start.
I would first break out Type, Location and Customer into separate dimension tables.
In terms of time intelligence modeling, I’m assuming you’re going to be hooking this up to the extended date table. Given that, it seems there is really one major decision to be made which is do you set the relationship from Dates[Date] to your Start Date and End Date as both inactive, or as active to Start Date and inactive to End Date?
The argument for the former is that it forces you to be explicit about which date you are using/filtering in every measure you write. The downside is that it then forces you to add a USERELATIONSHIP statement to every measure.
My inclination would be to connect to Start Date with an active relationship, and then create an inactive relationship between Dates[Date] and End Date. You’ll just need to be mindful to switch over via USERELATIONSHIP when you want Date to filter End Date instead of Start Date.
I hope this is helpful.
– Brian
Brian’s recommendation gets my vote - although personally I would choose End Date for the primary connection, with Start Date as the inactive relationship.
But, that goes back to the fact that my reports are often dealing with a sales cycle, with the date of the invoice (end date) being of primary importance.
So, I think the question for you is which date is more important?
