Forum Discussion

ptmuldoon's avatar
ptmuldoon
Resolver I
5 years ago
Solved

Filter for Last X Months

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))

 

 

 

 

 

Need Help
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    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.

4 Replies

  • v-yingjl's avatar
    v-yingjl
    Community Support
    5 years ago

    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.

  • smpa01's avatar
    smpa01
    Community Champion
    5 years ago

    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

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    5 years ago

    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

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    5 years ago

    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