Forum Discussion

sridharpolina's avatar
sridharpolina
Helper I
4 years ago
Solved

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-March20...
DAX
Desktop
Show and Tell
  • johnt75's avatar
    johnt75
    4 years ago

    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

lbendlin's avatar
lbendlin
Super User
4 years ago

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's avatar
    sridharpolina
    Helper I
    4 years ago

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

    • s0174804's avatar
      s0174804
      New Member
      2 years ago

      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!