Dynamic Filter with 3 dates

[teaspecial]

Hi everybody, 

I've tried to find the solution for my question for awhile and decided to ask the community 🙂

I have thousands of reports in excel files. I need the query to dynamically filter the last report, the one month before that and one year before that. Additional complexity is that every report is coming out on Friday, so i cannot just put exactly one month or one year before, i need to calculate the nearest Friday to that. 

I've tried to find solutions and there are couple of good options, but they use between filter and i need to find the way to be exactly on the dates and have 3 different ones.

My thought is that it should combine: 

Date.EndofWeek(Date.From(DateTime.FixedLocalNow()),-2)

But I still cannot find the exact query to filter all 3 reports?

Any help will be highly appreciated! Thank you!

[v-jingzhang · Accepted solution]

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.

[teaspecial]

Thank you! This is really great! 🙂 

[lbendlin]

 i need to calculate the nearest Friday to that.

Please be more specific.  What does "nearest" mean?  Can it be before the eqivalent date or does it always have to be after?  Please describe your rules.

[teaspecial]

In this regard the most logical will be to calculate the nearest before, not after.