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

     

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