Dynamic Filtering based on date selection

Hi GQ00 

I would recommend creating a calculation group for this. With a calculation group, you would create a calculation item for each of the Customer filter options, then filter on the required calculation item to apply that logic to any required measures.

Based on your description, the expressions for each of the calculation items would be something like:

-- 1. All Customers
SELECTEDMEASURE ()

--2. Active customers acquired last year - dynamic
VAR MinDate =
    MIN ( 'Dim Date'[Date] )
RETURN
    CALCULATE (
        SELECTEDMEASURE (),
        KEEPFILTERS ( MinDate > 'Dim Customers'[Acquisition Date] + 365 )
    )

--3. Active customers acquired last year - static
VAR MinDate =
    CALCULATE (
        MIN ( 'Dim Date'[Date] ),
        ALLSELECTED ( 'Dim Date' )
    )
RETURN
    CALCULATE (
        SELECTEDMEASURE (),
        KEEPFILTERS ( MinDate > 'Dim Customers'[Acquisition Date] + 365 )
    )

The 1st calculation item leaves all measures unchanged.

The 2nd & 3rd calculation items evaluate measures with a filter on 'Dim Customer'[Acquisition Date] intersected with the current filter context (due to KEEPFILTERS).

Once you have created the calculation group, you can filter on a calculation item however you like, such as with a single-selection slicer or filter pane.

Are you able to get something like this working?

Hey GQ00 , 

You don’t need calculated tables or calculation groups for this. The most efficient and flexible approach (especially in a composite model with DirectQuery fact) is to create a disconnected slicer table that controls a measure-based filter logic.

Since all visuals must respond to the customer filter, the cleanest pattern is:

1. Create a small disconnected table (e.g., “Customer Filter Type”) with three rows:

All Customers

Active Customers – Dynamic

Active Customers – Fixed

Use this table in a slicer.

2. Create a measure that evaluates which option is selected and returns 1/0 depending on whether a customer should be included. Then apply this measure as a visual-level (or page-level) filter set to “is 1”.

Your logic would look like this conceptually:

Get the minimum selected date from the date slicer

For Dynamic version → respect visual date context

For Fixed version → remove date context using ALL or ALLSELECTED on Date

Apply condition:
AcquisitionDate <= MinDate - 365
AND Status = "Active"

The difference between option 2 and 3 should indeed be context handling:

Dynamic → uses current filter context (dates on X-axis affect it)

Fixed → removes date context using ALL('Dim Date') so it only reacts to slicer selection

Avoid calculated tables here because:

They won’t react dynamically to slicer context.

They’ll increase model size unnecessarily.

They don’t play well with DirectQuery performance.

Also avoid switching relationships unless absolutely required — your current star schema is correct.

The key idea is:
👉 Let a disconnected slicer drive a measure.
👉 Use that measure as a filter.

This keeps everything dynamic, efficient, and compatible with DirectQuery.

If performance becomes an issue, you may want to precompute acquisition year or a boolean “Acquired Last Year” flag in the dimension table, but only if the dynamic calculation becomes too heavy.

If it solved your issue, feel free to mark it as the solution so others can benefit too.

Thanks for being part of the community.