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.

mahoneypat's avatar
mahoneypat
Microsoft Employee
6 years ago

I would add a WeekNumber column to your Date table and then use an expression like this (assuming you have weeknumber and year in your visual):

 

LastThreeWeeks =
VAR __selectedweek =
SELECTEDVALUE ( 'Date'[Week of Year] )
RETURN
CALCULATE (
[YourMeasure],
ALL ( 'Date'[Week of Year] ),
'Date'[Week of Year] < __selectedweek,
'Date'[Week of Year] > __selectedweek - 3
)

 

Note this will work within a given year.

 

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat