Today date to previous week date in Power Query Editor

[Krishna_Newuser]

Data will be refreshed once a week (Monday – 17-Jun-2024) and it will have previous week date (09-Jun-2024 to 15-Jun-2024). Likewise, it will continue to refresh on the forthcoming weeks.
I have the site closure date which has dummy date 31/12/9999 which considered as live in production but if any date updated near the store details like 11-Jun-2024 or 06-Jun-2024 it will be considered as closed.
Hence, I have planned to make some changes in power query editor to change the dummy date as previous week Monday date and whenever we have the previous week date in the site close date, let it have in the report. Once the report refreshed and moved to next week the date which I used for(Site Closure date) previous week date will be changed(stores are in production) and the store which has closing (previous week-June12) has to be filtered in Power query editor.
What I have done is I have added the Today date = DateTime.LocalNow()) but unable to convert this to previous week date, if that works the below if condition will help

if [Site Close Date] >= Date.From(Date.StartOfWeek(DateTime.LocalNow() - #duration(7, 0, 0, 0),Day.Sunday))

and [Site Close Date] <= Date.From(Date.StartOfWeek(DateTime.LocalNow()- #duration(1, 0, 0, 0),Day.Saturday))

then "Yes" else "No"

Can you please help with this data or any other idea would be helpful.

Store ID	Store Name	Site Close Date
1	AA	12/31/9999
2	AB	12/31/9999
3	AC	12/31/9999
4	BB	12/31/9999
5	CC	12/31/9999
6	DD	12/31/9999
7	EF	12/31/9999
8	ED	11-Jun-24

[ryan_mayu · Accepted solution]

@Krishna_Newuser 

you can try DateAdd to get previous date

=Date.AddDays( DateTime.LocalNow(),-7)

https://learn.microsoft.com/en-us/powerquery-m/date-adddays?wt.mc_id=DP-MVP-5004616

[Krishna_Newuser]

Thank you So much!!!

[ryan_mayu]

you are welcome