Forum Discussion

markefrody's avatar
markefrody
Post Patron
6 years ago
Solved

Filtering for Current Week and Past 2 Full Weeks

Hi.  I'm trying to filter for the current week except today and the past full 2 weeks. The relative date filtering option is not giving me the entire full weeks. For example, today is 5/21/2020, when...
Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    6 years ago

    Hi， markefrody 

    Create another calendar table"Date" then create  formulas as below:

    Today = date(2020,05,21)
    beginning day =
CALCULATE (
    MIN ( 'Date'[Date] ),
    FILTER ( ALL ( 'Table' ), 'Table'[Weeknum] = WEEKNUM ( [Today] ) - 2 )
)
    Ending day = [Today]-1
    visual control filter =
IF (
    ( SELECTEDVALUE ( 'Table'[Date ] ) > [beginning day] )
        && ( SELECTEDVALUE ( 'Table'[Date ] ) < [Today] ),
    1,
    0
)

    Then apply the measure "visual control filter" to the  filter pane:

     

    Here is a sample pbix file .

     

    Best Regards,
    Community Support Team _ Eason
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-easonf-msft's avatar
v-easonf-msft
Community Support
6 years ago

Hi， markefrody 

Create another calendar table"Date" then create  formulas as below:

Today = date(2020,05,21)
beginning day =
CALCULATE (
    MIN ( 'Date'[Date] ),
    FILTER ( ALL ( 'Table' ), 'Table'[Weeknum] = WEEKNUM ( [Today] ) - 2 )
)
Ending day = [Today]-1
visual control filter =
IF (
    ( SELECTEDVALUE ( 'Table'[Date ] ) > [beginning day] )
        && ( SELECTEDVALUE ( 'Table'[Date ] ) < [Today] ),
    1,
    0
)

Then apply the measure "visual control filter" to the  filter pane:

 

Here is a sample pbix file .

 

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.