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 adjust the date to be June 6, 2024) but this is a pain to keep adjusting and I wondered what would be a better way?  Here's the filter I'm currently using...could I alter this filter in some way?

 

= Table.SelectRows(#"Expanded std_case_service_file_individuals", each [event_start_timestamp] > #datetime(2024, 6, 9, 0, 0, 0))

 

 

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

     

4 Replies

  • 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

         

  • Ahmedx's avatar
    Ahmedx
    Super User
    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))