Forum Discussion
Working with dates from multiple tables
So, assuming that your date table does not have times and is in the format "yyyyMMdd", you should be able to create a new column in the table with the formula:
FormattedMigrationDate = FORMAT([MigrationDate],"yyyyMMdd")
Reference here: https://msdn.microsoft.com/en-us/library/ee634398.aspx
Then you can relate that table and the date table.
This is DAX, so this is in the data model.
Depending on what you are doing, this may be OK and you can use "USERELATIONSHIP" and specify the correct relationship. If not, you may have to create multiple date tables. I'd have to see your data and data model to know for sure.
I figured out that the data did not have migration date set to an actual date type. This allowed me to join the data appropriately using one date table per migration type. If there is a better way, i'd like to hear it!
I once experimented with having a central date table that I related to each of my individual date tables that were then related to the fact table. The idea was that I could at least use a central date table in all of my visualizations without having to worry about and remember which individual date table was related to a particular fact table column. It seemed to work but I think I saw some feedback that some people had some issues with this approach. Might be worth exploring.
Hi smoupre, I've been trying to approach my data model this way and running into an issue where I need to prioritize one a date from one table over another in certain instances. Did you run into something similar? An example would be I have social media campaign running that has a start date and then individual posts have their own dates. However, not all of the social media posts end up being associated with any particular campaign because they are just one off posts, etc. So when I display the results, I would like to see the date used by the campaign first and if it doesn't exist fall back to the date on the individual posts. Thoughts?
