Forum Discussion
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 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.
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]-1visual 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.
3 Replies
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
markefrody , I prefer to use week rank for this.
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.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]-1visual 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.
