Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Creating Relationships between Date Columns - 2 Data Tables and a Common Table for filtering

Hi   Hoping someone can help as this has been bugging me for hours on end now.   I have two tables, one with Orders and one with Quotes. Both tables have a date column for there estimated close d...
Need Help
Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi v-yulgu-msft

 

Please see some dummy Data below; 

 

 

 

 

 

 

 

 

 

 

 

 

 

So I have one table 'Sales1' With the Expected Closing column, along with table 2's 'Sales2'[ExpectedClosing] 

 

Then I have an automatically generated table that just contains dates from 01/01/2000 to 12/31/2019. 

 

Then I built a relationship from Sales1's dates and Sales2 dates to the Date column within 'CommonTable' 

 

Then I have visuals which are using both Sales1 and sales2 data and want the single filter using the 'CommonTable'[Dates] Column to be able to limit the visual results affecting both tables with a single filter. 

 

The relationship works in a fashion but only seems to be linking the dates as the same intermittently. 

 

Hoping that's clear and not too rambly. 

 

Thanks in advance.

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi, 

 

If it helps please see below the relationship table, settings and the code I am using for the Date_Filter table (common date table for the filter) 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

The code I am using for the Date_Filter table is; 

 

Date_Filter =
ADDCOLUMNS (
CALENDAR (DATE(2000,1,1), DATE(2018,31,9)),
"DateAsInteger", FORMAT ( [Date], "MM/DD/YYYY" ),
"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" )
)

 

Then the relationship is with the "Date" column. 

 

Then in PowerBI itself, I have made sure all three date columns are formatted and categorised in the same manner; 

Data Type: Date/Time 

Format: MM/DD/YYYY

 

Any advise would be freatly appreciated. 

 

Thanks again