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.

amitchandak's avatar
amitchandak
Super User
6 years ago

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-Last-Week/ba-p/1051123

 

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-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.