cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Rienster Regular Visitor
Regular Visitor

relative date filtering start and end date -1 year

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:
relativefilter_1.JPG
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? 
1 ACCEPTED SOLUTION

Accepted Solutions
Rienster Regular Visitor
Regular Visitor

Re: relative date filtering start and end date -1 year

@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 🙂 

View solution in original post

2 REPLIES 2
Community Support Team
Community Support Team

Re: relative date filtering start and end date -1 year

Hi @Rienster ,

 

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.

1.png

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.

Rienster Regular Visitor
Regular Visitor

Re: relative date filtering start and end date -1 year

@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 🙂 

View solution in original post

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,286)