cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
msommerf
Helper II
Helper II

Power Query to Query Data for this week only - Week Starts on a Sunday

Good morning all,

 

Please can someone assist with the following?

I am new to Power Query and this is driving me crazy.

I am attempting to query data and my dataset is very large. I am looking to pull data for this week only.

I have attempted to use Date.IsInCurrentWeek but this returns data with Monday as the start of the week.

My week starts on a Sunday.

I have attempted to use:

= Table.AddColumn(#"Expanded {0}1", "Is This Week", each Date.IsInCurrentWeek([Inspection Due],Day.Sunday))

but this throws up an error.

Is there a way to query the data so I get this weeks data Sunday through to Saturday?

Any assistance appreciated.

1 ACCEPTED SOLUTION
BA_Pete
Super User
Super User

Hi @msommerf ,

 

All of the Date.IsInCurrent/NextXXX etc. functions use the system clock/region settings to determine week start/end dates. I assume you are trying to report on financial week but run your system on a regular Mon-Sun working week.

 

Try something like this:

 

if
[date] >= Date.StartOfWeek(Date.From(DateTime.LocalNow()), Day.Sunday)
and
[date] <= Date.EndOfWeek(Date.From(DateTime.LocalNow()), Day.Sunday)
then 1
else null

 

 

Pete

View solution in original post

2 REPLIES 2
BA_Pete
Super User
Super User

Hi @msommerf ,

 

All of the Date.IsInCurrent/NextXXX etc. functions use the system clock/region settings to determine week start/end dates. I assume you are trying to report on financial week but run your system on a regular Mon-Sun working week.

 

Try something like this:

 

if
[date] >= Date.StartOfWeek(Date.From(DateTime.LocalNow()), Day.Sunday)
and
[date] <= Date.EndOfWeek(Date.From(DateTime.LocalNow()), Day.Sunday)
then 1
else null

 

 

Pete

View solution in original post

This is perfect. Many thanks for your help 🙂

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors