Summary Date Filter Across Tables

Hi Anonymous ,

Yes, you need a Date Table to bridge both tables, but the key challenge is aligning different date granularities (daily for the first table and monthly for the second). Here’s how to structure your model:

Step 1: Create a Date Table
Since you need only month-level selection, create a Date Table with Month Start Dates:

DateTable =
ADDCOLUMNS(
CALENDAR(DATE(2020,1,1), DATE(2030,12,31)),
"MonthYear", FORMAT([Date], "MMYYYY"),
"MonthStart", EOMONTH([Date], -1) + 1
)

This table includes:

A full date range (adjust as needed).
A MonthYear column (MMYYYY) for easy filtering.
A MonthStart column to align date logic.

Step 2: Connect Tables to Date Table
Now create relationships:

ITEMS Table (Start Date & End Date):

Connect DateTable[MonthStart] → ITEMS[Start Date] (Many-to-One, Inactive)
Connect DateTable[MonthStart] → ITEMS[End Date] (Many-to-One, Inactive)

FINANCIALS Table (Monthly Data):

Connect DateTable[MonthYear] → FINANCIALS[Date] (Many-to-One, Active)
Since ITEMS has a date range, you'll need to use USERELATIONSHIP in measures.

Step 3: Create Measures
To filter ITEMS between the selected period:

ItemsInRange =
VAR StartMonth = MIN(DateTable[MonthStart])
VAR EndMonth = MAX(DateTable[MonthStart])
RETURN
CALCULATE(
COUNTROWS(ITEMS),
ITEMS[Start Date] <= EndMonth,
ITEMS[End Date] >= StartMonth
)

For FINANCIALS, a simple measure:

FinancialsFiltered =
CALCULATE(
SUM(FINANCIALS[Amount]),
USERELATIONSHIP(DateTable[MonthYear], FINANCIALS[Date])
)

Step 4: Set Up the Filter
Use a Slicer on DateTable[MonthYear] to let users select a range.
The measures ensure correct filtering.

Expected Outcome
Selecting Feb 2024 - June 2024:
ITEMS Table returns entries active within that range.
FINANCIALS Table returns monthly data for selected months.

Please mark this post as solution if it helps you. Appreciate Kudos.