Forum Discussion
Relationship on Date between two different tables
After you've created your date table; you can go to the relationship builder and link the tables to the date table. You will then be able to use the datekey for example from your date table as the "Date" component as it will be a perfectly setup date list for you.
asilver1982 Check the formatting of the dates in Power BI, make sure they aren't Text.
Otherwise the better solution is to create a date table. This will ensure that all dates in both tables are accounted for (continous days). There are a number of ways to do this, but building this in DAX is pretty straightforward.
Just set the dates you want in the initial section. * The provided DAX solution below was from another thread here, I just can't find it atm.
Date =
ADDCOLUMNS (
CALENDAR (DATE(2000,1,1), DATE(2025,12,31)),
"DateAsInteger", 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" ),
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )
)
Anonymous Thanks for that info. Where do I enter the Dates of each column that I am using?
ElliotPPost Prodigy
After you've created your date table; you can go to the relationship builder and link the tables to the date table. You will then be able to use the datekey for example from your date table as the "Date" component as it will be a perfectly setup date list for you.