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.
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.
Thanks for the reply. I did try that and it just doesn't seem to accept the relationship.
Could it be a problem since most of the migrated dates are blank?
I'm beggining to think that the problem with joining using the new date table is maybe how I created it? I just ripped something off the web.
Anybody see anything wrong with using this a date "dimension" table?
DimMigrationDate = ADDCOLUMNS ( CALENDAR ( "1-jan-2015", "31-dec-2020" ), "DateID", FORMAT ( [Date], "YYYYMMDD" ), "Year", YEAR ( [Date] ), "Monthnumber", FORMAT ( [Date], "MM" ), "YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ), "YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ), "MonthNameShort", FORMAT ( [Date], "mmm" ), "MonthNameLong", FORMAT ( [Date], "mmmm" ), "WeekNumber", WEEKNUM ( [Date], 1 ), "DayOfWeekNumber", WEEKDAY ( [Date] ), "DayOfWeek", FORMAT ( [Date], "dddd" ), "DayOfWeekShort", FORMAT ( [Date], "dddd" ), "Quarter", "Q" & FORMAT ( [Date], "Q" ), "YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ) )One thing that is sticking out at me is that there is no column "Date" - this is what I'm using to create my relationship. Sorry if this is all banter but I'm just not familiar with DAX.
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.