Using time in a Date column for measures but using date only for relationships.

[maracles]

I have various date columns which I originally set as 'Date' rather than 'Date/Time' columns so that I could easily build relationships with my Date table. 

Now however they want more precise measures. So measures which were previously based on whole days they want based on hours, however I can't change my date columns to Date/Time otherwise they will lose the relationship with the Calendar Date.

Is there a way to have the column function as both? So from a relationship point of view the calendar table sees a whole date, but from the measure point of view I am calculating based on the full date/time value.

I know I could add each column twice to my tables and use one for relationships and one for measures but this feels inefficient. 

Thanks. 

[tonysellars]

One approach you might take is to "snowflake" the model by adding a new datetime dimension between the existing date dimension and the fact table.  This way it would still slice by the current dimension but you would be able to have the separate ability to slice at a more granular level when needed.  It would keep your existing reports functional as well since they would not care about the new intermediate table.  This is similar to the structure where you would add a month or year dimension on the outside of the snowflake when handling granularity differences across tables. 

[maracles]

Thanks for your input @tonysellars. how exactly would this work - not sure I'm quite understanding. as I'm relatively new to this.  

So my traditional date table would be on the outside of the snowflake, with an intemediary date table between that and my fact table? Wouldn't the relationships still rely on the date column being a date rather than date/time (i.e. a whole number rather than decimal.). 

I need to create a measure calculating the time differance between two date columns (which I can do when I set them as date/time). I can then use this duration measure in various other measures and visualisations.

At the same time I want to use the same date/time columns that I am using for my measures to build a relationship with my calendar table. This though won't work if the column is date/time.  

Thanks. 

[tonysellars]

Take the following example for a simple table for incoming messages:

Initially Messages would be related to the Date through the DateKey

datekey = YEAR([messagedate])*10000 + MONTH([messagedate])*100 + DAY([messagedate]) 

The new inserted table brings the grain lower by now includeing a DateHourKey

datehourkey = YEAR([messagedate])*1000000 + MONTH([messagedate])*10000 + DAY([messagedate])*100 + HOUR([messagedatetime])

By inserting DateHour between Messages and Date - all the previously existing items which operated on day will still work, you just now have the option to a drill through to the hour level as well.

Hope this helps.