Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Krishna_Newuser
Helper II
Helper II

Today date to previous week date in Power Query Editor

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 IDStore NameSite Close Date
1AA12/31/9999
2AB12/31/9999
3AC12/31/9999
4BB12/31/9999
5CC12/31/9999
6DD12/31/9999
7EF12/31/9999
8ED11-Jun-24

 

Krishna_Newuser_0-1718720308826.pngKrishna_Newuser_1-1718720331349.png

 

 

 

 

1 ACCEPTED SOLUTION
ryan_mayu
Super User
Super User

@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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

3 REPLIES 3
ryan_mayu
Super User
Super User

@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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thank you So much!!!

you are welcome





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors