Relative date filtering including future dates

[sridharpolina]

Hi, I am trying to dynamically filter a date which includes future dates as well. For Ex. For March 2022 I need a query which looks at the previous 12 months and the next 12 months (March2021-March2022(current) - March 2023. I tried using the relative date filter in Power BI but there is not 'between' option that could give me the above desired result.

Also Is there a Analytical pane line I can conditionally format (attached image) where I could code the lines as dashed after the current date on the lines on the y-axis. I tried to create it I was only able to shade the area in the x-axis line. Any help is greatly appreciated. 

[johnt75 · Accepted solution]

You could add a column to your date table for filtering purposes, something like

Within 24 month window =
var startDate = EOMONTH( TODAY(), -13) + 1
var endDate = EOMONTH( TODAY(), 12 )
return IF( 'Date'[Date] >= startDate && 'Date'[Date] <= endDate, 1, 0)

then add that filter to your visuals

[mayasandler]

I was facing a similar issue and couldn't find a solution. Your suggestion is a great workaround. Thanks! 

[sridharpolina]

Hi,

Thanks for your input regarding the dasheded line that worked. For the in between dates I am currently applying a manual filter as shown in the image to filter the date to show the view 'past 4 quarters > Current Date < future 4 quarters but i need to dynamically change the quarter based on current date instead of being a static filter. Attached is the manual filter I applied and a sample dataset.

[lbendlin]

You can achieve a solid+dashed appearance by stitching two measures together so that one (past data) stops where the other (future data) begins.

Please provide sanitized sample data that fully covers your issue.

Please show the expected outcome based on the sample data you provided.

[sridharpolina]

@lbendlin I included a sample file via the link. Please let me know if that works for writing the DAX query.

[s0174804]

Hi,

Unfortunately the proposed solution does not work for me as I am calculating moving/rollings averages and % change versus historical dates/averages with measures using data on dates well before the period I want to ultimately visualise. 

If I apply the proposed filter on the visual, the measures are not calculated correctly or at all as none of the historical data is taken into account.

I need to differentiate between the calculation of the measures and the visualisation.

Does anyone have a work-around? 

Thank you!