Relative Date Filter to include any dates in the past plus next 3 months

[kristi_in_heels]

Hello,

I need to apply a filter to a column, so that the results pull any dates in the past, plus the next 3 months. The relative filter works for the 3 month component, however I'm unsure how to also pull anything that is in the past into this list.

Is there a measure that could be written that I can apply as a filter instead of using the relative date?

Thank you

[divyed]

Hello @kristi_in_heels ,

Assuming you have  a slicers on date , You can use below dax to achieve your purpose :

// Slicer takes a value Date from table Dates 

1. If you want relative date as selected date + 90 days , use

Relative_Date =

SELECTEDVALUE(Dates[Date]) + 90   // assuming 

2. if you want relative date as selected date + number of month, use 

   

   Relative_Date 1 = EDATE(SELECTEDVALUE(Dates[Date]), 3)

You can replace number if days or months as per need, I would sugget to use parameter if required.

I hope this helps. 

Please mark this as solution if this works. 

Cheers

[Anonymous · Accepted solution]

Hi @kristi_in_heels ,

You might want to consider creating a new calculated column by subtracting 3 months from all the dates. Once you have this new column, you can apply a filter to retrieve all records where the new date is prior to today. This way, you will effectively get all the original dates that fall within the past three months.I'v made a test for your reference:

1\ I assume there is a table

2\Add a new caculate column

NewDate = EDATE('Table'[ExecutionStartDate],-3)

3\Filter on NewDate

Best Regards,

Bof

[kristi_in_heels]

Thank you. Some of our dates are in excess of 12 months in the past, would this -3mo calculation still work?

[Anonymous]

Hi @kristi_in_heels ,

Yes. You could adjust the date range according to your specific needs. In the screenshot I provided, the time range is set to include data from the past 10 years.

Best Regards,

Bof

[kristi_in_heels]

Thanks so much.

Would you be able to assist me in tweaking this measure so I can use it to apply a conditional format?

Currently, I use this to highlight anything in "FS" phase, but I need the highlight to apply to that same condition for anything in "FS", "PFS" or "CON".

TG EXE Before Today = if(max('All Projects'[ProjectPhaseDescription])="FS"&& max('All Projects'[ExecutionStartDate_Forecast])<TODAY(),"#d64550")

[vicky_]

Are you looking for a solution in PowerQuery (M) or in PowerBI (DAX)?

Power query actually does have a filter for "In the next"... from which you can set to 3 months. Right click on the column, and you should see it. I don't remember off the top of my head whether those 3 months are months or calendar months, but you should be able to easily tweak the formula (i.e by using days)

If you're after a DAX solution, I would recommend one of two ways - 
the first would be to not use a calculated column unless absolutely necessary. Instead, consider using calculate functions such as DATESBETWEEN(), DATESINPERIOD() etc... to filter out the dates as needed.

If you absolutely need to use a calculated column, then something like:

Flag in Past or next 3 Months = INT(Table[Date] <= EOMONTH(TODAY(), 3))

note that EOMONTH will give you the end of the calendar month, so again, please tweak as needed.

Hope this is helpful

[kristi_in_heels]

Thank you.

I am already using the filter for the next 3 months, but this cuts off anything that is noted as before today's date.

What I want to see is anything within this filter, PLUS anything that has a date showing prior to today, so we can see items which haven't hit the target date.