Just a quick post on how you can create a date column on which you can create your relationship to you date table, you should us this when you have time values in your datetime field. To know why check out the powerpivot time intelligent function golden rules
So we need to remove the time element from the datetime column, we can do this by creating a new calculated column where we create a new date value based on the year, month and day values from the original datetime field. This will look like:
=date(year(FactInventory[UpdateDate]),MONTH(FactInventory[UpdateDate]),DAY(FactInventory[UpdateDate]))
That’s it
Thankyou for this post. I have searched for hours to figure out why my fact table would not relate to my date table, turns out the fact table had various times in it which made it incompatible with the date table. Creating a clean date column fixed the issue.
=DATEVALUE([UpdateDate]) appears to work for me.