Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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
Proud to be a Datanaut!
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
Proud to be a Datanaut!
This is perfect. Many thanks for your help 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
13 | |
11 |