Filter for Last X Months

[ptmuldoon]

I am pulling in data from json files that contains data for the last XX years.  Currently the file is only through 2019 data, that will change as more is added.

And I'm trying to then filter that data down to the Last X months.  I tried to filter it down to the last 3 using InPreviousNMonths, but think have an issue trying to set a starting date?

Should the below work to get just the last 3 months?   I do hope to make that date more dynamic eventually, but just learning at this point.

#"Filtered Rows" = Table.SelectRows(#"Changed Type", each Date.IsInPreviousNMonths(#date(2019,12,1), 3))

[v-yingjl · Accepted solution]

Hi @ptmuldoon ,

Considering the updated date in your table, you can create query like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc0xDgAgDALAvziblKIOvsX4/2+oi1LHC5SOkQjCnFY8zfzoLVLkvxCplwhd/CGFXavdqCiKqrgD+/EbONjJXA==", 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}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date", Order.Descending}}),
    EndDate = Date.From(List.Max(#"Sorted Rows"[Date])),
    StartDate = 
    Date.AddMonths(EndDate,-2),
    FilterRows = 
    Table.SelectRows(
        #"Sorted Rows",
        each [Date] >= StartDate and [Date] <= EndDate
    )
in
    FilterRows

Attached a sample file in the below, hopes to help you.

In addition, you can refer this blog: “In the Previous” Date Filters In Power BI/Get&Transform/Power Query 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jimmy801]

Hello @ptmuldoon 

you can just use the GUI to get what you want

The function used here has time intelligence and uses the current date of your system and filters all dates that are witin the last 3 months

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[mahoneypat]

Typically you would put your [Date] column where you have a hard-coded date now #date(2019,12,1) so it is dynamic whenever you refresh.  If you want data since a fixed date, you can just filter for greater than that date instead.

Pat

[smpa01]

@ptmuldoon  can you try this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtNQ1MAQipVgdGNcIlWuMyjVB5Zqics1QueaoXAtUriUK19AAlYvqKkOIq2IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}}),
    #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Column1]), Int64.Type),
    #"Grouped Rows" = Table.Group(#"Inserted Year", {"Year"}, {{"ad", each _, type table [Column1=nullable date, Year=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let x =[ad],
       #"Inserted Month" = Table.AddColumn(x, "Month", each Date.Month([Column1]), Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Inserted Month", "Maxmonth", each List.Max(#"Inserted Month"[Month])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Filter", each ([Maxmonth]-[Month])+1),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each [Filter] >= 1 and [Filter] <= 3),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Column1"})
in
    #"Removed Other Columns"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"ad"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Column1"}, {"Column1"})
in
    #"Expanded Custom"

from

to here with the code above