Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
10 years ago
Solved

Working with dates from multiple tables

There were a few posts on this but none that hit exactly what I need.   I have two tables - both are migration dates but one table is for PC migration, the other is for a Person that is being migra...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    10 years ago

    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.

     

Greg_Deckler's avatar
Greg_Deckler
Community Champion
10 years ago

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.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    10 years ago

    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?

    • Anonymous's avatar
      Anonymous
      Not applicable
      10 years ago

      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.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        10 years ago

        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!