Filter Dates in Previous Week

[2366]

Hello,

How do I filter dates in last week (Monday to Sunday) in DAX? 

Thanks

[v-jayw-msft · Accepted solution]

Hi @2366 

Check the formulas.

weekdays = WEEKDAY('Table'[date],2)

weekstart = 'Table'[date]-'Table'[weekdays]+1

lastweekstart = 'Table'[weekstart]-7

Combine three formulas:

Column =

var week_day = WEEKDAY('Table'[date],2)

var week_start = 'Table'[date]-week_day+1

return

week_start-7

Best Regards,

Jay

[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/

[amitchandak]

@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-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8

[2366]

I 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