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 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 ...
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'

     

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'

 

  • JohnFabric's avatar
    JohnFabric
    Helper I
    9 months ago

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