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

This is perfect. Many thanks for your help 🙂

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Top Solution Authors