cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sridharpolina
Helper I
Helper I

Relative date filtering including future dates

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. Image3.jpg

1 ACCEPTED SOLUTION
johnt75
Super User
Super User

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

View solution in original post

4 REPLIES 4
johnt75
Super User
Super User

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

sridharpolina
Helper I
Helper I

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.Image4.jpg

 

lbendlin
Super User
Super User

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.

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

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors