Forum Discussion

MNHGaming's avatar
MNHGaming
Frequent Visitor
1 year ago
Solved

Date Sorting (relative or by formula)

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!

Need Help
  • audreygerred's avatar
    audreygerred
    1 year ago

    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
    )

     

1 Reply

  • audreygerred's avatar
    audreygerred
    Super User
    1 year ago

    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
    )