Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Greetings,
I'm trying to build a report where I want to filter from data from last week only however the Date.IsInPreviousWeek function only returns data from the prior Sunday-Saturday. I need my week defined as Monday-Sunday. Is there a way to manipulate this function to give me data for the desired range without having to add additional columns?
Solved! Go to Solution.
Unfortunately, as @ImkeF points out, there isn't a parameter to modify this function to work like you want it to.
However, you can write your own equivalent function using functions that do have such a parameter, like Date.StartOfWeek.
This should give you the start of the current week:
Date.StartOfWeek(Date.From(DateTime.LocalNow()), Day.Monday)
Then you can write a custom column like
[Date] >= Date.AddWeeks([WeekStart], -1) and [Date] < [WeekStart]
Here's a full query version that defines WeekStart as a constant rather than materializing it as an custom column:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTQt9Q3MjAyUorVAfOACIVriMo1QuUao3JNULmmqFwzVK45KtcCyo0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
#"Added Weekday" = Table.AddColumn(#"Changed Type", "Weekday", each Date.DayOfWeekName([Date]), type text),
WeekStart = Date.StartOfWeek(Date.From(DateTime.LocalNow()), Day.Monday),
#"Added IsInPrevWeek" = Table.AddColumn(#"Added Weekday", "IsInPrevWeek", each [Date] >= Date.AddWeeks(WeekStart, -1) and [Date] < WeekStart, type logical)
in
#"Added IsInPrevWeek"
Unfortunately, as @ImkeF points out, there isn't a parameter to modify this function to work like you want it to.
However, you can write your own equivalent function using functions that do have such a parameter, like Date.StartOfWeek.
This should give you the start of the current week:
Date.StartOfWeek(Date.From(DateTime.LocalNow()), Day.Monday)
Then you can write a custom column like
[Date] >= Date.AddWeeks([WeekStart], -1) and [Date] < [WeekStart]
Here's a full query version that defines WeekStart as a constant rather than materializing it as an custom column:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTQt9Q3MjAyUorVAfOACIVriMo1QuUao3JNULmmqFwzVK45KtcCyo0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
#"Added Weekday" = Table.AddColumn(#"Changed Type", "Weekday", each Date.DayOfWeekName([Date]), type text),
WeekStart = Date.StartOfWeek(Date.From(DateTime.LocalNow()), Day.Monday),
#"Added IsInPrevWeek" = Table.AddColumn(#"Added Weekday", "IsInPrevWeek", each [Date] >= Date.AddWeeks(WeekStart, -1) and [Date] < WeekStart, type logical)
in
#"Added IsInPrevWeek"
Hi @OpenDesk_BL ,
this behaviour depens on the locale that your file is based on.
So if you could change from US to UK for example, it would take Monday as the first day of the week.
Unfortunately, there is no parameter in the function itself that allows you to limit it just to this formula.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Check out the July 2025 Power BI update to learn about new features.