Forum Discussion
Dynamic Filtering based on date selection
Thanks for sharing the files, and yes unfortunately direct file attachments are not available to all users. Last I checked it was Super Users only but that may have changed 😞
From what I can see, all that potentially needs to be fixed is the definition of which Acquisition Dates to include relative to the "min date" (whether fixed or relative). I may have confused the definition with my earlier suggestion.
Should the Acquisition Date be at least 1 year earlier than the "min date"?
If so, these calculation item definitions seem to give your expected result.
I've used EDATE as well since it adds/subtracts a specificed number of months regardless of days per month.
/* =========================================================================== Cust Dynamic =========================================================================== */ VAR MinDate = MIN ( 'DATE'[ActualDate] ) VAR CompDynamic = CALCULATE ( SELECTEDMEASURE ( ), KEEPFILTERS ( CUSTOMERS[AcquisitionDate] <= EDATE ( MinDate, -12 ) && CUSTOMERS[Status] = "Active" ) ) RETURN CompDynamic /* =========================================================================== Cust Fixed =========================================================================== */ VAR MinDate = CALCULATE ( MIN ( 'DATE'[ActualDate] ), ALLSELECTED ( 'DATE' ) ) VAR CompFixed = CALCULATE ( SELECTEDMEASURE ( ), KEEPFILTERS ( CUSTOMERS[AcquisitionDate] <= EDATE ( MinDate, -12 ) && CUSTOMERS[Status] = "Active" ) ) RETURN CompFixedIf anything, the condition on CUSTOMERS[AcquisitionDate] should be the only thing to adjust.
Also, if it is good enough to check if a customer exists in SALES, I would also suggest adjusting Cust_CountSales as follows:
Cust_CounSales = COUNTROWS ( SUMMARIZE ( SALES, CUSTOMERS[CustomerID] ) )Is this closer to a solution?
Hi MohdZaid_ , thanks for your time and feedback.
I tried following your steps, relying on AI for #2. I ended up with a disconnected filter for the three filter types, and a measure with a switch that looks for the selected filter and applies the relevant logic.
However it seems to be showing a similar behaviour as Owen's solution, in the sense that fixed cohort is exactly the same as dynamic.
Is there a limitation in Pbi that I'm not aware of?
Additionally, not sure how I can filter the page when relying on a measure, and filtering each visual is not viable at this stage.
Is there anything I'm missing?
Hey GQ00 ,
This is not a Power BI limitation, but it usually happens because the date context is not being removed correctly.
In your “Fixed” logic, you must explicitly remove all date context coming from visuals, not just slicer context.
Very important distinction:
ALL('Dim Date') → removes all date filters (including slicer)
ALLSELECTED('Dim Date') → keeps slicer, removes visual context
No modifier → fully respects current visual context (dynamic behavior)
For your scenario:
Dynamic → use the current filter context normally
Fixed → compute MinDate using ALLSELECTED('Dim Date') so it only reacts to the slicer, not to axis context
If you used ALL('Dim Date'), both versions can accidentally evaluate the same way depending on how MinDate is calculated.
You’re right — applying the measure to every visual is not scalable.
Unfortunately, Power BI does not allow measures as model-level filters.
However, you have two clean options:
Option 1 (Recommended): Use the measure in the Filter Pane at Page Level
You can:
Drag the measure into the Page filter pane
Set it to is 1
This applies to all visuals on the page without touching each one.
This works perfectly with disconnected slicers.
Option 2 (More Advanced / More Efficient)
Instead of filtering visuals, embed the logic inside your base measures.
For example:
Instead of:
[Total Sales]
Use:
CALCULATE(
[Total Sales],
FILTER( Customers, [Customer Cohort Measure] = 1 )
)
This guarantees every measure respects the cohort logic automatically — but requires refactoring measures.
For large composite models, this is often more stable and predictable.