Forum Discussion

jiji's avatar
jiji
Frequent Visitor
4 years ago
Solved

Filter table starting from Current Week to 12 more future weeks

Hi Team - Could you please help me how to expose this filter using DAX? I need to show values that always starts in current week Monday to Sunday to 12 more future weeks. I was only able to create "Current week & Next week" as per below:

Column =
VAR next =
    TODAY () + 7
VAR yn =
    YEAR ( next )
VAR yt =
    YEAR ( TODAY () )
VAR weeknumt =
    WEEKNUM ( TODAY () )
VAR weeknun =
    WEEKNUM ( next )
VAR wek =
    WEEKNUM ( 'date'[Date] )
RETURN
    IF (
        OR (
            yn = YEAR ( 'date'[Date] )
                && weeknun = wek,
            yt = YEAR ( 'date'[Date] )
                && weeknumt = wek
        ),
        "current week & next week",
        BLANK ()
    )
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi jiji ,

    I have created a simple sample, please refer to it to see if it helps you.

    Create a column.

    Column =
VAR _weeknum =
    WEEKNUM ( 'date'[Date], 1 )
VAR _weeknumtoday =
    WEEKNUM ( TODAY (), 1 )
RETURN
    IF (
        _weeknum >= _weeknumtoday
            && _weeknum <= _weeknumtoday + 12,
        "currentWk to 12 more future wks",
        BLANK ()
    )

    Then you can filter the column is not blank.

    If I have misunderstood your meaning, please provide more details with your desired output and your pbix file without privacy information.

     

    Best Regards

    Community Support Team _ Polly

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    I am not sure how your Dates Table looks like, but I tried to create a sample like the attached.

    Please check the below DAX formula for creating a new column and the attached pbix file.

     

    Expected outcome CC =
VAR _addweeknumbercol =
    ADDCOLUMNS (
        Dates,
        "@weeknumber", WEEKNUM ( Dates[Date] ),
        "@year", YEAR ( Dates[Date] )
    )
VAR _futuredate =
    TODAY () + 84
VAR _weeknumberaftereightyfourdays =
    MAXX ( FILTER ( _addweeknumbercol, Dates[Date] = _futuredate ), [@weeknumber] )
VAR _yearnumberaftereightyfourdays =
    MAXX ( FILTER ( _addweeknumbercol, Dates[Date] = _futuredate ), [@year] )
VAR _lastdateaftereightyfourdays =
    MAXX (
        FILTER (
            _addweeknumbercol,
            [@weeknumber] = _weeknumberaftereightyfourdays
                && [@year] = _yearnumberaftereightyfourdays
        ),
        Dates[Date]
    )
RETURN
    IF (
        Dates[Date] >= TODAY ()
            && Dates[Date] <= _lastdateaftereightyfourdays,
        "currentWk to 12 more future wks",
        "Others"
    )
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi jiji ,

    I have created a simple sample, please refer to it to see if it helps you.

    Create a column.

    Column =
VAR _weeknum =
    WEEKNUM ( 'date'[Date], 1 )
VAR _weeknumtoday =
    WEEKNUM ( TODAY (), 1 )
RETURN
    IF (
        _weeknum >= _weeknumtoday
            && _weeknum <= _weeknumtoday + 12,
        "currentWk to 12 more future wks",
        BLANK ()
    )

    Then you can filter the column is not blank.

    If I have misunderstood your meaning, please provide more details with your desired output and your pbix file without privacy information.

     

    Best Regards

    Community Support Team _ Polly

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.