Date Filter for last 2 months Data

[TARUNEY]

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.

[v-junyant-msft · Accepted solution]

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.

[watts_jim]

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.

[dufoq3]

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]

@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]

From the documentation :

"Note that this function will return false when passed a value that occurs within the current month."

Date.IsInPreviousNMonths - PowerQuery M | Microsoft Learn