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

 

I will need to report data that shows how many PC and people are being migrated per day, and how man actually got migrated (these are two more date columns).

 

My thoughts are to create a date table and have each table join to the date table - many to one relationship.  However I cannot connect by date as the dates have time in them also.  I tried sending them stright to int's to join the data by using:

FORMAT(TABLE[COLUMN], "YYYMMDD") however this doesn't seem to actually convert the data.

 

Can someone point out an easier way to do this, or help with the correct DAX expression to use?

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.

     

6 Replies

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