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

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!