Forum Discussion

JohnFabric's avatar
JohnFabric
Helper I
9 months ago
Solved

Corellating dates between 2 tables

Hello, I have 2 tables that look like this

 

Table 1

Closed DateID
1/1/2025 1:00:00PM1234
1/2/2025 1:00:00PM

4567

 

Table 2

Closed DateID
1/1/2025 1:05:00PM9998
1/2/2025 1:10:00PM9997

 

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?

Need Help
  • MasonMA's avatar
    MasonMA
    9 months ago

    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'

     

2 Replies

  • MasonMA's avatar
    MasonMA
    Super User
    9 months ago

    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'