Forum Discussion

TARUNEY's avatar
TARUNEY
Frequent Visitor
2 years ago
Solved

Date Filter for last 2 months Data

I am trying to filter the data  on the date field (Resolved Date) from the last 2 month from the current date. I am using the below custom column for the same with the expression 

= Table.AddColumn(#"Changed Type", "Custom", each Date.IsInPreviousNMonths([Resolved Date],2))

I am getting the wrong result in the custom column. Example for the date 02-03-2024 the result should be true but it was showing as False

 

Update on 14th March: I am using the Direct Query Mode

Can someone help me on the same.

Thanks in Advance.

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi TARUNEY ,

    Have you solved your problem? The function Date.IsInPreviousNMonths will return false when passed a value that occurs within the current month.
    Date.IsInPreviousNMonths - PowerQuery M | Microsoft Learn


    Please try this:
    Here is my sample data and today is 2024.3.25 and I test it in Direct Query mode:


    Please use this M function to add a custom column:

    if [Resolved Date] >= Date.AddMonths(DateTime.Date(DateTime.LocalNow()), -2) and [Resolved Date] <= DateTime.Date(DateTime.LocalNow()) and [Resolved Date] <> null then true else if [Resolved Date] = null then null else false

     

    And the final output is as below:



    Here is the whole M function in the Advanced Editor:

    let
    Source = Sql.Databases("VM0"),
    test1 = Source{[Name="test"]}[Data],
    dbo_test = test1{[Schema="dbo",Item="test"]}[Data],
    #"Added Custom" = Table.AddColumn(dbo_test, "Custom", each if [Resolved Date] >= Date.AddMonths(DateTime.Date(DateTime.LocalNow()), -2) and [Resolved Date] <= DateTime.Date(DateTime.LocalNow()) and [Resolved Date] <> null then true else if [Resolved Date] = null then null else false)
in
    #"Added Custom"


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

5 Replies

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    Hi TARUNEY, check these 2 versions and modify the code with your needs.

     

    • v1 compares [Date] vs start of month -2 months i.e. [Date] = 12/04/2024 vs 01/02/2024
    • v2 considers also day i.e [Date] 12/04/2024 vs 12/02/2024

    Result

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDSNzDWNzIwMlGK1YlWMjTQB4oguEBZQySuISrXVN8QrNhYKTYWAA==", 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}}),
    Ad_v1 = Table.AddColumn(#"Changed Type", "v1", each Date.StartOfMonth([Date]) >= Date.AddMonths(Date.StartOfMonth(Date.From(DateTime.LocalNow())), -2), type logical),
    Ad_v2 = Table.AddColumn(Ad_v1, "v2", each [Date] >= Date.AddMonths(Date.From(DateTime.LocalNow()), -2), type logical)
in
    Ad_v2
    • TARUNEY's avatar
      TARUNEY
      Frequent Visitor
      2 years ago

      dufoq3  Thank for the Solution, I tried to implentment implement the same, but getting the issue as I am using Direct Query mode. Any solution approach suggestions for the direct query mode. 

       

  • watts_jim's avatar
    watts_jim
    Helper II
    2 years ago

    If you want to include dates in the current month, then you need to use a different function.  Also, you don't say whether you want to go back by date or by whole months, so here's something to try:

    Date.IsInPreviousNMonths( [Resolved Date] , 2) or (

      Date.IsInCurrentMonth( [Resolved Date] ) and (Date.Day( [Resolved Date] ) <= Date.Day( DateTime.LocalNow() ) )

     

    Depending on your source, it might be as easily done in the initial query that retrieves the data.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi TARUNEY ,

    Have you solved your problem? The function Date.IsInPreviousNMonths will return false when passed a value that occurs within the current month.
    Date.IsInPreviousNMonths - PowerQuery M | Microsoft Learn


    Please try this:
    Here is my sample data and today is 2024.3.25 and I test it in Direct Query mode:


    Please use this M function to add a custom column:

    if [Resolved Date] >= Date.AddMonths(DateTime.Date(DateTime.LocalNow()), -2) and [Resolved Date] <= DateTime.Date(DateTime.LocalNow()) and [Resolved Date] <> null then true else if [Resolved Date] = null then null else false

     

    And the final output is as below:



    Here is the whole M function in the Advanced Editor:

    let
    Source = Sql.Databases("VM0"),
    test1 = Source{[Name="test"]}[Data],
    dbo_test = test1{[Schema="dbo",Item="test"]}[Data],
    #"Added Custom" = Table.AddColumn(dbo_test, "Custom", each if [Resolved Date] >= Date.AddMonths(DateTime.Date(DateTime.LocalNow()), -2) and [Resolved Date] <= DateTime.Date(DateTime.LocalNow()) and [Resolved Date] <> null then true else if [Resolved Date] = null then null else false)
in
    #"Added Custom"


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