Filtering in query by last week

[jschnur]

 I Need last week to start on last Monday and go through Sunday. 

Is there a place or method to set the configuration to start the week on monday  instead of starting it on sunday.

There must be configuration parameters somewhere to define the start of week.

The Specific example I am querying form an excel file where a column is date and it has the entire years data i am trying to make a graph of last week. I need it to be last monday thru last sunday. it currently returns last sunday through last saturday.

[MarcelBeug · Accepted solution]

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"

[v-ljerr-msft · Accepted solution]

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.  

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

Regards

[MarcelBeug · Accepted solution]

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"