Working with dates from multiple tables

[Jasel]

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?

[Greg_Deckler · Accepted solution]

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.

[Jasel]

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?

[Jasel]

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.

[Jasel]

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!

[Greg_Deckler]

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.

[boujin]

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?