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 ...
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
    )

     

audreygerred's avatar
audreygerred
Icon for Super User rankSuper 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
)