Forum Discussion

JohnFabric's avatar
JohnFabric
Icon for Helper I rankHelper I
10 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
    10 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
Icon for Super User rankSuper User
10 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
    Icon for Helper I rankHelper I
    10 months ago

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