Dynamic Date Filter

[mcolb88]

I have all of my visualzations based on a date range. I show a variety of measures based on the date, but want to limit the date to the range between the start and end date of a campaign. I have a slicer that allows the user to pick the cam paign and the campaiogn dimension has start and end dates. How can I limit (filter) the visualization, page and/or report to only show dates in the range of the selected campaign?

There doesn't seem to be an oiption for a dynamic filter (really unfortunate).

Trying to figure out a way to use a DAX CalculatedTable and a relationship to my date dimension but haven;'t gotten that to work yet either.

Any suggestions are greatly appreciated.

[DoubleJ]

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

[mcolb88]

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.

[Anonymous]

Hi , was a solution founded for this problem ?

[DoubleJ]

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

[parry2k]

May be add a calculate field on your calendar table called, "Within Campaign Date Range" and add filter on this field, check the syntax for following

WithinCampaignDateRange 
= if(Calendar[Date]>=Campaign[StartDate] && Calendar[Date]<=Campaign[EndDate], 1, 0)

Now on filter drop WithinCampignDateRange and put condition is 1

It will only show dates where value is 1. Just an idea.

[mcolb88]

Funny, had that thought too...

DateInCampaign =IF([Call Date] >= [Campaign Start Date] && [Call Date] <= [Campaign End Date], TRUE(), FALSE())

[Campaign Start Date] and [Campaign End Date] are measures calauclated as the min() and Max() of thoise dates in the scenario where mutliple campaigns are selected.

So I drop a table on a report showing the Call Date and DateInCampaign.

When I select a campaign, in the slicer, these Campaign Start and End change, but the values for DateInCampaign do not 😞

Thanks for the idea, this should work though right? Or are the calculated columns re-calculated at process time?