Dynamic Filter with 3 dates

Hi teaspecial 

You can get the nearest Friday by

= Date.AddDays(Date.EndOfWeek(Date.From(DateTime.LocalNow()),Day.Saturday),-7)

For one month before that and one year before that, I guess you want to filter to exact nearest Friday dates either? You can use below code.

= Date.AddDays(Date.EndOfWeek(Date.From(DateTime.LocalNow()),Day.Saturday),-7-5*7)

= Date.AddDays(Date.EndOfWeek(Date.From(DateTime.LocalNow()),Day.Saturday),-7-53*7)

To filter a Date column to only contain above three dates, you can add a custom step to filter rows. For example, create three variables with above code first. Then add a custom step to filter rows with three variables. 

This is the full code for your reference:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VdJBDoMwDETRu7Cu5HicEDgL6v2v0VAhMX/5lDGysa9ryxY6Q025fT+LGQNKkcxqNyoKuj/tnKA6S9MY/6geZHcpoeOVojsyIQQ1XpWXFcoKZYWyHuk48IKcBJ2vRuyOLJcaZL9kj3JkgxCUzTa94+kdT3Q80fFEx0cMRwpCUDbbenCcjmSuXGWjrTEnhLtoPIyGy1g77ZAv+SazvuZ7wkffHw==", 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}}),
    nearestFriday = Date.AddDays(Date.EndOfWeek(Date.From(DateTime.LocalNow()),Day.Saturday),-7),
    nearestOneMonthAgo = Date.AddDays(Date.EndOfWeek(Date.From(DateTime.LocalNow()),Day.Saturday),-7-5*7),
    nearestOneYearAgo = Date.AddDays(Date.EndOfWeek(Date.From(DateTime.LocalNow()),Day.Saturday),-7-53*7),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Date] = nearestFriday or [Date] = nearestOneMonthAgo or [Date] = nearestOneYearAgo)
in
    #"Filtered Rows"

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.