Forum Discussion
Relationship on Date between two different tables
Hello All,
I am having issues getting two different tables to join a relationship between dates. I and tracking banner ads and I am trying to compare DFA and DFP number as you can see below. My tables work by themselfs but they do not work when I try to combine them together. Please see the below and let me know if someone can help. I can send the data file if needed too.
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.
3 Replies
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.
