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

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
Icon for Community Support rankCommunity 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.