Summary Date Filter Across Tables

[Anonymous]

I'm having trouble figuring out what kind of date table (if thats what I want) to build a filter for users to customize a report contents

I have two tables, one has START DATE (ddmmyyyy) and END DATE (ddmmyyyy) for ITEMS and the second has a DATE (mmyyyy) for FINANCIALS. 

I want the user to be able to pick the report period, and have the dashboard return contents from both tables, and I would like the user to only be able to choose at the month (not the day) level. 

So the user would choose February 2024 - June 2024 and the filter would return everything from Feb 1 to June 30 from the first table, and the February through June data from the second table. 

I think what I need is a date table to connect the two that I'd filter on, but I'm not getting the kind of results I'm expecting so I'm obviously missing something 

[FarhanJeelani · Accepted solution]

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.