Forum Discussion
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(...
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 falseAnd 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.
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