Forum Discussion
Dynamic Filtering based on date selection
- 7 months ago
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 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?
- GQ007 months ago
Helper III
Hi Owen, thanks for your reply.
Your approach is so simple and easy to follow, I'm impressed.
I've gone ahead and created a new calculation group, with a precedence =1. The CG includes 3 calculation items based on your response. I've then used the column in a single select slicer.
The All and dynamic filters are working as intended, the static however, is not.
For the static I was expecting to see a fixed amount of customers, depending on the minimum selected date from the slicer; however a bar chat with a date on X axis shows this cohort grows over time.
I'm not an expert but maybe the issue lies in the fact that the X axis of the visual redefines the min date despite using the all selected?
The image attached below is a visual representation of the issue. 28 customers in 2024; however only 23 were active with 12 months of trading in Jan 2024. I was expecting the chart to always show 23; not continue growing to 28.
Let me know if the above does not make sense and I can work towards producing a sample dataset, since I can't share the org's data for obvious reasons.
- OwenAuger7 months ago
Super User
Glad to see that the calculation group approach is at least partially working!
To figure out what's going on in the static case, a sample model with dummy data, along with expected measure results would be helpful (when you can put one together).
In the meantime, could you share these:
- Definition of the measure on the y-axis of the column chart
- Semantic model diagram
In case it's any help, I put together an example using a Contoso Data Generator sample PBIX.
I adjusted the calc item "filter definition" for my version so the Dynamic calc item expression is:
VAR MinDate = MIN ( 'Date'[Date] ) RETURN CALCULATE ( SELECTEDMEASURE (), KEEPFILTERS ( Customer[Acquisition Date] >= MinDate - 365 && Customer[Acquisition Date] < MinDate ) )(I realise you have to add the "Active" filter as well.)
- GQ007 months ago
Helper III
Thanks for sticking this one out.
Sample Data & PBIX attached.
Put it simply, Cust 1 comps from Dec 25; Cust 2 fr Feb 26.
I was expecting the Cust Fixed filter to filter based on min date from slicer, (Jan 26 in this case) but Cust 2 is still in the visual; 😵.
Similarly , Cust 3 should only appear in Cust Dynamic not Cust FixedGosh I'm struggling to add the attachments...