Forum Discussion
Helper IFilter Dates in Previous Week
Hello,
How do I filter dates in last week (Monday to Sunday) in DAX?
Thanks
Hi 2366
Check the formulas.
weekdays = WEEKDAY('Table'[date],2) weekstart = 'Table'[date]-'Table'[weekdays]+1 lastweekstart = 'Table'[weekstart]-7Combine three formulas:
Column =var week_day = WEEKDAY('Table'[date],2)var week_start = 'Table'[date]-week_day+1returnweek_start-7Best Regards,Jay
5 Replies
- amitchandak
Super User
2366 , You need a column like this in you date table
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format
Weekday = WEEKDAY([Date],2) //Monday week
measures
This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))Last Week Sunday = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1 && 'Date'[Weekday] =7 ))
Last Week Monday= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1 && 'Date'[Weekday] =1 ))
Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8
2366I have something similar which works if all the dates in last week are in the same year but if you have dates from two different year in previous week the above solution wont work?
For example - last week dates include 27/12/2021 to 02/01/2022
- amitchandak
2366 , it should, as we are using week rank, not the week number
- VahidDM
HI 2366
Check this link:
https://www.vahiddm.com/post/weekly-time-intelligence-dax
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/ Hi 2366
Check the formulas.
weekdays = WEEKDAY('Table'[date],2) weekstart = 'Table'[date]-'Table'[weekdays]+1 lastweekstart = 'Table'[weekstart]-7Combine three formulas:
Column =var week_day = WEEKDAY('Table'[date],2)var week_start = 'Table'[date]-week_day+1returnweek_start-7Best Regards,Jay
