Date Sorting (relative or by formula)

[MNHGaming]

I would like to know if it's possible to sort on a BI Visual all of THIS YEAR and the next three months. This should be relative to "today". My table has full dates: day+month+year, month, year, and year + month values.

I don't know BI syntax so well, but maybe something like:

ColumnYear = Year(Today())

OR
(ColumnYear = Year(Today())+1 AND (ColumnMonth = Jan OR ColumnMonth = Feb OR ColumnMonth = Mar))

I am new-ish to Power BI, so please feel free to dumb down any answers. I'm not even sure where I'd put the code snippet above. I just know the intent 😄

I tried using the relative date filters (I was willing to accept 6 months behind and 6 months ahead), but I can't find a way to look both forward and back using the built-in filters, maybe I was using them wrong? Below is my filter set that returns no data (I assume it's using AND not OR on the filters)

Thank you!

[audreygerred · Accepted solution]

Hi! Add this as a calculated column to your date table, then use it as a filter and filter to 1:

IsInDateRange =

VAR TodayDate = TODAY()

VAR BeginningOfYear = DATE(YEAR(TodayDate), 1, 1)

VAR EndOfNextThreeMonths = EOMONTH(DATE(YEAR(TodayDate), 12, 31), 3)

RETURN

IF(

    'DateTable'[Date] >= BeginningOfYear && 'DateTable'[Date] <= EndOfNextThreeMonths,

    1,

    0

)