topic Re: Relative date filtering including future dates in DAX Commands and Tips

Relative date filtering including future dates

sridharpolina — Thu, 17 Mar 2022 20:52:15 GMT

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.

Re: Relative date filtering including future dates

lbendlin — Fri, 18 Mar 2022 11:57:46 GMT

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.

Re: Relative date filtering including future dates

sridharpolina — Tue, 22 Mar 2022 13:27:27 GMT

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.

Re: Relative date filtering including future dates

sridharpolina — Mon, 21 Mar 2022 14:09:07 GMT

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

Re: Relative date filtering including future dates

johnt75 — Mon, 21 Mar 2022 15:31:35 GMT

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

Re: Relative date filtering including future dates

mayasandler — Thu, 29 Dec 2022 01:03:57 GMT

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

Re: Relative date filtering including future dates

s0174804 — Wed, 04 Oct 2023 13:49:22 GMT

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!