Previous Week Tuesday Date

[Anonymous]

Hi Everyone,

Is there any way to find previous week "Tuesday" date by using Power Query.
I have a requirement to filter table for every refresh & It should contains only 1 week data( Data from Tuesday to monday).
I need to create a variable and it should contains previous week "Tuesday" date, based on variable I can filter the table.

Thanks in advance

[v-rzhou-msft · Accepted solution]

Hi @Anonymous ,

Please add the "Filter Row" step in my M code to yours.

...
#"Filtered Rows" = 
    let _WeekStart = Date.AddDays(Date.StartOfWeek(Date.From(DateTime.FixedLocalNow()), 2),-Date.DayOfWeek(Date.From(DateTime.FixedLocalNow()), Day.Tuesday)),
        _WeekEnd = Date.AddDays(_WeekStart,6)
    in
    Table.SelectRows(#"Changed Type", each [Date] >= _WeekStart and [Date] <= _WeekEnd)
...

The step above "Filter Row" step names #"Change Type", please change the step name as your before step name.

The whole M code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc67DcAwDAPRXVgLsCT/ZzG8/xohUoVIea+6c5CeWcKLBwzhuPaxpHWlShpKjbSUOqkqDdJUmqSttEhNaf/HmIZUev9x7wM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Value", Int64.Type}}),
    #"Filtered Rows" = 
    let _WeekStart = Date.AddDays(Date.StartOfWeek(Date.From(DateTime.FixedLocalNow()), 2),-Date.DayOfWeek(Date.From(DateTime.FixedLocalNow()), Day.Tuesday)),
        _WeekEnd = Date.AddDays(_WeekStart,6)
    in
    Table.SelectRows(#"Changed Type", each [Date] >= _WeekStart and [Date] <= _WeekEnd)
in
    #"Filtered Rows"

My Sample:

After filter my table will only show data from 2022/10/11 to 2022/10/17 until next Tuesday 2022/10/18.

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , if you need based on today , these two dates can help

Date.AddDays(Date.StartOfWeek(Date.From(DateTime.FixedLocalNow()), 2),-7)

Date.StartOfWeek(Date.From(DateTime.FixedLocalNow()), 1)

[amitchandak]

@Anonymous , Try a new column like

Last Tuesday = Date.AddDays(Date.StartOfWeek([Date], 2),-7)

This Tuesday = Date.StartOfWeek([Date], 2)

Next Tuesday = Date.AddDays(Date.StartOfWeek([Date], 2),7)

for today use Date.From(DateTime.FixedLocalNow()), in place of date column