PowerPivot: Creating a clean date column to create your relationship upon

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

2 Replies to “PowerPivot: Creating a clean date column to create your relationship upon

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

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.