Filtering for Current Week and Past 2 Full Weeks

[markefrody]

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 I use relative date filtering for the last 3 weeks excluding today, it will give me date range of 4/30/2020 - 5/20/2020. What I need is 3 weeks excluding today (5/21/2020) which should give me:  5/3/2020 - 5/20/2020. Is there any way to do this instead of using the relative date filter?

Appreciate your kind assistance.

Best regards,

Mark V.

[v-easonf-msft · Accepted solution]

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.

[amitchandak]

@markefrody , I prefer to use week rank for this.

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last 12 week Sales = CALCULATE(SUM(Sales[Net Sales]),FILTER(all('Date'),'Date'[Week Rank]>=min('Date'[Week Rank])-12 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

Last 2 Week Sales =
Var _min = maxx(allselected('Date','Date'[Week Rank]) -2
Var _max = maxx(allselected('Date','Date'[Week Rank]) 
CALCULATE(SUM(Sales[Net Sales]),FILTER(all('Date'),'Date'[Week Rank]>=_min && 'Date'[Week Rank]<=_max))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[mahoneypat]

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