Filtering in query by last week

The start of the week can be influenced by options - Regional Settings - Locale.

E.g. Dutch (Netherlands) has Monday as first day of the week.

To be independent of these settings, you can use function Date.StartOfWeek to calculate the first date of a week.

This function has a parameter for the first day of the week:

Date.StartOfWeek(dateTime as any, optional firstDayOfWeek as nullable number) as any

Example code with various options:

let
    Source = List.Dates(#date(2017, 5, 15), 30, #duration(1, 0, 0, 0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Date.IsInPreviousWeek", each Date.IsInPreviousWeek([Date])),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Date", type date}, {"Date.IsInPreviousWeek", type logical}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "StartOfCurrentWeek", each Date.StartOfWeek(DateTime.Date(DateTime.LocalNow()),Day.Monday)),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "In Previous week", each [Date] >= Date.AddDays([StartOfCurrentWeek],-7) and [Date] < [StartOfCurrentWeek], type logical)
in
    #"Added Custom2"

Hi jschnur,

return_type

A number that determines the return value: use 1 when the week begins on Sunday; use 2 when the week begins on Monday. The default is 1. Return type: 1, week begins on Sunday. Weekdays are numbered 1 through 7. Return type: 2, week begins on Monday. Weekdays are numbered 1 through 7.

---

In addition, you should also be able to set the return_type value of WEEKNUM Function (DAX) to 2 in this scenario. :smileyhappy: 

=WEEKNUM("Feb 14, 2010", 2)

Regards