Forum Discussion
Date.IsInPreviousWeek Start Week with Monday
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?
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"
2 Replies
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.
