Forum Discussion
Solved
Corellating dates between 2 tables
Hello, I have 2 tables that look like this Table 1 Closed Date ID 1/1/2025 1:00:00PM 1234 1/2/2025 1:00:00PM 4567 Table 2 Closed Date ID 1/1/2025 1:05:00PM 9998 ...
Hi,
You can simply create one Date table to filter these two tables. With DAX you can use a sample code like
Calendar = ADDCOLUMNS( CALENDAR(DATE(2020,1,1), DATE(2030,12,31)),--adjust your date range "Year", YEAR([Date]), "Month", FORMAT([Date], "MMM"), "YearMonth", FORMAT([Date], "YYYY-MM") )In Power Query, change 'Closed Date' from Table1 and Table2 to 'Date' data type so that the Calendar table can filter them properly.
Build a relationship as below.
Create measures using COUNTROWS if IDs are unique.
T1 Count =COUNTROWS(Table1)Then you can plot them in 'Stacked Column Chart'
Hi,
You can simply create one Date table to filter these two tables. With DAX you can use a sample code like
Calendar =
ADDCOLUMNS(
CALENDAR(DATE(2020,1,1), DATE(2030,12,31)),--adjust your date range
"Year", YEAR([Date]),
"Month", FORMAT([Date], "MMM"),
"YearMonth", FORMAT([Date], "YYYY-MM")
)In Power Query, change 'Closed Date' from Table1 and Table2 to 'Date' data type so that the Calendar table can filter them properly.
Build a relationship as below.
Create measures using COUNTROWS if IDs are unique.
T1 Count =
COUNTROWS(Table1)
Then you can plot them in 'Stacked Column Chart'
Thank you! Creating the third table worked like a charm.