Forum Discussion

cb_wcc's avatar
cb_wcc
New Member
1 year ago
Solved

Date filter last 3 months

Hi there!   I'm trying to filter for data that is from the last 3 months.  Currently I'm using the filter below and adjusting the date each week to be 3 months ago (ie today is Sept 6, 2024 so I ad...
Need Help
  • Ahmedx's avatar
    Ahmedx
    1 year ago

    or try this

    = Table.SelectRows(#"Expanded std_case_service_file_individuals", each [event_start_timestamp] >= Date.AddMonths( Date.From(DateTime.LocalNow()),-3))
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi cb_wcc ,
    You can try this 

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XdMxjsMwEEPRu6RewB5KNO2zBLn/NRbbLPhTstLHPOj9fu1jDp3ar8/P31CP1WP3cI+rR3rcPZ4ec2KhYRAxqBhkDDoGIYOSQcqgRWgR74EWoUVoEVqEFqFFaBFaVrW4adw0bho3jZvGTeOmcdO4aQwag8agMWgMGoPGoDFoDBqDxqAxaAwag8agMWgMGoPGoDFo/EWzquU6MNRj9dg93OPqkR53jwePnlgMQsSgYpAx6BiEDEoGKYMWoUW8B1qEFqFFaBFahBahRWhpqDRNmiZNk6ZJ06Rp0jRpmjRNQBPQBDQBTUAT0AQ0AU1AE9AENAFNQBPQBDQBTUAT0AQ0AU2+aPoP3QeGeqweu4d7XD3S4+7x4NETi0GIGFQMMgYdg5BBySBl0CK0iPdAi9AitAgtQovQIrQILQstDfUcGOqxeuwe7nH1SI+7x4NHTywGIWJQMcgYdAxC/qE+vw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    FilteredRows = Table.SelectRows(Source, each Date.From([Date]) > Date.AddMonths(Date.From(DateTime.LocalNow()), -3))
in
    FilteredRows

    Final output

    Best regards,
    Albert He

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

     

Ahmedx's avatar
Ahmedx
Super User
1 year ago

pls try this

 

 

= Table.SelectRows(#"Expanded std_case_service_file_individuals", each Date.IsInPreviousNMonths([event_start_timestamp], 3))

 

 

cb_wcc's avatar
cb_wcc
New Member
1 year ago

Thanks so much!! This did filter for the previous 3 months but was including all of June, so I modified the query as follows to show the last 90 days (approx 3 months) and it works!!

 

= Table.SelectRows(#"Expanded std_case_service_file_individuals", each Date.IsInPreviousNDays([event_start_timestamp], 90))

 

Not sure if there's another way to filter to exactly 3 months from the current date, but if not, 90 days works for me. 🙂 

 

I did try your other suggestion as well but I got this error:

 

Expression.Error: We cannot apply operator < to types Date and DateTime.
Details:
Operator=<
Left=6/06/24
Right=1/17/05 3:00:00 PM

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi cb_wcc ,
    You can try this 

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XdMxjsMwEEPRu6RewB5KNO2zBLn/NRbbLPhTstLHPOj9fu1jDp3ar8/P31CP1WP3cI+rR3rcPZ4ec2KhYRAxqBhkDDoGIYOSQcqgRWgR74EWoUVoEVqEFqFFaBFaVrW4adw0bho3jZvGTeOmcdO4aQwag8agMWgMGoPGoDFoDBqDxqAxaAwag8agMWgMGoPGoDFo/EWzquU6MNRj9dg93OPqkR53jwePnlgMQsSgYpAx6BiEDEoGKYMWoUW8B1qEFqFFaBFahBahRWhpqDRNmiZNk6ZJ06Rp0jRpmjRNQBPQBDQBTUAT0AQ0AU1AE9AENAFNQBPQBDQBTUAT0AQ0AU2+aPoP3QeGeqweu4d7XD3S4+7x4NETi0GIGFQMMgYdg5BBySBl0CK0iPdAi9AitAgtQovQIrQILQstDfUcGOqxeuwe7nH1SI+7x4NHTywGIWJQMcgYdAxC/qE+vw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    FilteredRows = Table.SelectRows(Source, each Date.From([Date]) > Date.AddMonths(Date.From(DateTime.LocalNow()), -3))
in
    FilteredRows

    Final output

    Best regards,
    Albert He

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