Forum Discussion
Dynamic Date Filter
Following is my suggestion.
I created a small sample consisting of 2 tables. Please don't be confused by the German date format, it's read dd.mm.yyyy.
First table contains the campaings:
Second table contains the data to be analyzed, in my sample a simple sales table:
You can create a simple slicer with the campaings, as you already did.
In your measures you can add a test condition that checks whether there's excatly ONE campaign selected in your slicer. The trick is to use the HASONEVALUE() function. If it returns TRUE (meaning there is one campaign selected), narrow the results of your calculation with the start and end date of the selected campaign. Otherwhise just ignore the dates. A measure that sums up the Amounts looks like this:
SalesAmount = IF(
HASONEVALUE(Campaigns[CapaignId]),
CALCULATE(
SUM(Sales[Amount]),
Sales[SalesDate] >= VALUES(Campaigns[StartDate]),
Sales[SalesDate] <= VALUES(Campaigns[EndDate])),
SUM(Sales[Amount]))The results with different slicer selections:
1. No slicer selection:
2. "Campaign 1" selected:
3. "Campaign 2" selected:
This approach works if the user can only select one campaign in the slicer. If multiple selection is allowed this sample has to be enhanced.
Hope this helps!
JJ
JJ,
Appreciate the quick response. However, what I need to do is to limit the visualization from displaying dates past the end of the campaign. The x-axis is the date and the y-axis is two percentages. Once the campaign is over, the values will never change. The values are cumulative so every date has a value. I supposed I could check the date in the Measure and return blank once the end of the campaign is reached, then the chart may stop displaying dates past the end. This is what it looks like.
This is a perfect example of adding a dynamic filter for the visualzation to be Call Date <= [Campaign End Date]. Other tools hve this ability.
- DoubleJ9 years agoSolution Supplier
I see your point now.
I also would try to return blank values once the campaing end date is reached.
Hope you'll find a solution!
JJ
- Anonymous7 years agoNot applicable
Hi , was a solution founded for this problem ?