Corellating dates between 2 tables

[JohnFabric]

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
1/2/2025 1:10:00PM	9997

Importantly, the ID's and 'Closed Date' fields are NOT shared between the two tables, they are measuing two seperate things. However, I would like to display them both on the same graphic with a count of how many each per month there are, ie:

Jan 2025: 100 values from Table 1, 209 values from Table 2

Feb 2025: 90 values from Table 1, 109 values from Table 2

But I havent figured out a way to display this data in a relational way. When I attempt to put them on the same graphic whatever table's date field I use shows up properly, but then the other value appears as a flat line

I think I have to associate the dates somehow to get it to display correctly. I tried extracting the month and year from each date, merging those into a column in each table, and associating those columns in a many:many relationship, but that didnt give me quite the right result and I'm not sure many:many is the correct way to do this. Any ideas on how to get this to display correctly?

[MasonMA · Accepted solution]

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'

[JohnFabric]

Thank you! Creating the third table worked like a charm.