relative date filtering start and end date -1 year

[Anonymous]

I have a dashboard that I want to make dynamic so I don't have to put the start and end dates in for every visual. 

My tables are from a database and work in directquerie. 

I have a table with campaigns, which has a name, products and start and end date. 

When I select the campaign name, it will show the products in a slicer, which filter the whole report. 

What I want is that by selecting the campaign, also the measures in my visuals select the correct start and end date. 

This I got working. But in some visuals I also want to show the previous year in comparison. 

Here are some formulas I've used: 

quantitycurrent = CALCULATE(

[TotalQuantity];

FILTER('Date 2';'Date 2'[Date]>=MIN('Campaign'[Starting Date])&& 'Date 2'[Date] <=MAX('Campaign'[Ending Date]

)))

quantityprevious =

CALCULATE(

[TotalQuantity];

SAMEPERIODLASTYEAR('Date 2'[Date]))

Which shows me this:

When I add a date filter in the bar on the right it works and shows only the time where its bluegreen, but I want it to be relative so I don't have to add the dates for every campaign to every visual. 
I've tried other options as well, by trying to add the same min and max filter as in the first measure but with a dateadd.. but its not allowed. 
I've also tried with Variables but I don't think I'm doing it correctly. Also tried a slicer but it only allows 1 date collumn. And also I don't really want to slice the dates, I want it to work automatically by selecting the campaign so the people working with it can't make mistakes in selecting the right dates.  

Anybody have some new ideas for me to try? 

[v-xuding-msft]

Hi @Anonymous ,

I’m confused about your requirement. How to select the right date? Do you mean filter date in visual level filter? Do you want to show the value of last year between the date you selected? I created a sample using your formula. And it was display correctly using a relative type slicer. I can’t reproduce your issue. Can you please share more details and some sample data ? then we can help you quickly.

Best Regards,

Xue Ding 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

@v-xuding-msft 

sorry for the late reply. We already solved this issue in a quick and dirty way in the SQL database they made for this project. Don;t ask me to explain how hahaha, but it works how I want it 🙂