Help With Date Filters

[Anonymous]

I have this report I'm working on for the president of our company. He wants to be able to open the report and see sales opportunities forcasted to close in 9-18 months on one tab, 3-6 months on another, and then 0-3 months on the final tab. I know I can use the CloseDate column for the filter but I can't figure out how to make it filter into these ranges. I know I can setup manual ranges where we populate the dates but he wants it to be automatic. Right now I have it filtered for the next 18 months on the first tab but he doesn't want anything closing in months 0-8 included in it. 

I thought about creating a column where it's a DATEADD where it adds the 9 months and use that as one filter. Then use the CloseDate column to filter to the 18 months. BUT I can't use DATEADD in DirectQuery. Any other ideas? Thanks!

[v-frfei-msft]

Hi @Anonymous ,

We can use page level Relative date filtering in your reprot.

[Anonymous]

Ok, I managed to fix the date range issue. 

I created a column called Phase1EndDate and Phase1StartDate. I then set the formulas to look like this:

Phase1StartDate = Opportunities[CloseDate]>(TODAY() + 270)

and

Phase1EndDate = Opportunities[CloseDate]<(Today() + 540)

I then used the Filters to filter the Close Dates to be in this range.

However, here's a new issue. As I'm looking at this, our president is going to want the Phase1StartDate to be the first of the month and the Phase1EndDate the end of the month, regardless of the current date. So, how do I get the filter to look 9 months out but grab the first of that month and how do I get the filter to look 18 months and get the last of that month?

Any ideas?

[Anonymous · Accepted solution]

Figured it out!

Phase1StartDate = Opportunities[CloseDate]> DATE(YEAR(Today()+270),MONTH(TODAY()+270),1)

(If anyone has a better solution to below, please let me know. This does not predict leap years and I haven't figured that out yet either.)

Phase1EndDate = Opportunities[CloseDate]< DATE(YEAR(TODAY() + 540), MONTH(Today() + 540),DAY(IF(MONTH(TODAY()+540)=2,28,IF(MONTH(TODAY()+540)=1,31,IF(MONTH(TODAY()+540)=3,31,IF(MONTH(TODAY()+540)=4,30,IF(MONTH(TODAY()+540)=5,31,IF(MONTH(TODAY()+540)=6,30,IF(MONTH(TODAY()+540)=7,31,IF(MONTH(TODAY()+540)=8,31,IF(MONTH(TODAY()+540)=9,30,IF(MONTH(TODAY()+540)=10,31,IF(MONTH(TODAY()+540)=11,30,IF(MONTH(TODAY()+540)=12,31))))))))))))))

[Anonymous]

Right, except if you set up the relative date filter for in the next 18 months, you are also getting data for the next 8 months too. He wants 0-3 months on one tab, 4-8 on another, and then 9-18 on another. 

So, there can't be anything in the date range of 0-8 months on the tab that is 9-18 months.

Does that make sense?