Forum Discussion
Date Filter for last 2 months Data
Hi Anonymous ,
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.
Community ChampionHi Anonymous, 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_v2dufoq3 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.