Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Help to filter Current week and next Week

I have dates column in a table. How can i filter the current week And next week. Thanks in Advance.. Have a great day
Show and Tell
Tips and Tricks
  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    Hi Anonymous ,

     

    Sorry for my late respond, we can adjust the formula as below.

    Column = 
VAR next =
    TODAY () + 7
VAR yn =
    YEAR ( next )
VAR yt =
    YEAR ( TODAY () )
VAR weeknumt =
    WEEKNUM ( TODAY (),2 )
VAR weeknun =
    WEEKNUM ( next,2 )
VAR wek =
    WEEKNUM ( 'date'[Date],2 )
RETURN
    IF (
        OR (
            yn = YEAR ( 'date'[Date] )
                && weeknun = wek,
            yt = YEAR ( 'date'[Date] )
                && weeknumt = wek&& WEEKDAY('date'[Date],2)<=5
        ),
        "current week & next week",
        BLANK ()
    )

     

     

    As the picture above, Sat and Sunday have been exculded for current week.

     

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Anonymous Please create a calculated column as per below. If the week number is equivalent to current week number or week number one plus one then return true. you can use this column in filter.

Column = 
VAR _weeknum = WEEKNUM(TODAY())
RETURN IF(WEEKNUM('Table'[Date])=_weeknum||WEEKNUM('Table'[Date])= _weeknum+1,TRUE(),FALSE())

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Thanks

 

Is there any option to filter only monday to friday in current week

i.e sunday and saturday to be excluded..

 

According to your query week is starting from sunday how to start from monday?

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    Sorry for my late respond, we can adjust the formula as below.

    Column = 
VAR next =
    TODAY () + 7
VAR yn =
    YEAR ( next )
VAR yt =
    YEAR ( TODAY () )
VAR weeknumt =
    WEEKNUM ( TODAY (),2 )
VAR weeknun =
    WEEKNUM ( next,2 )
VAR wek =
    WEEKNUM ( 'date'[Date],2 )
RETURN
    IF (
        OR (
            yn = YEAR ( 'date'[Date] )
                && weeknun = wek,
            yt = YEAR ( 'date'[Date] )
                && weeknumt = wek&& WEEKDAY('date'[Date],2)<=5
        ),
        "current week & next week",
        BLANK ()
    )

     

     

    As the picture above, Sat and Sunday have been exculded for current week.