Forum Discussion

OpenDesk_BL's avatar
OpenDesk_BL
New Member
3 years ago
Solved

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 m...
  • AlexisOlson's avatar
    3 years ago

    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"