Include only Latest 3 months of data

[henrycqc]

In power query, I want to limit the amount of data loaded into the model by only including new data within the last 3 months from a dataflow.  So far found this YouTube video “ https://youtu.be/zr52Q00SrNM?t=137 ” which almost works

Q. How can I amend the formula below to return exactly 3 months from the current date?

- Is there a different approach that you would suggest in power query to reduce how much data is loaded?

Example:

• I want only the latest rolling 3 month:                                  18/08/2021 – 18/11/2021
• But I get this when I amend the YouTube tutorial:                 01/08/2021 – 18/11/2021

Power Query settings I used:

Query 01 :  

let

Date = {Number.From(#date(2021,1,1))..Number.From(#date(2021,11,18))},
#"Converted to Table" = Table.FromList(Date, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "dates"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"dates", type date}})
in
#"Changed Type"

Query 02 :

let
Source = Dates,
#"Filtered Rows" = Table.SelectRows(Source, each Date.IsInPreviousNMonths([dates], 3) or Date.IsInCurrentMonth([dates]))
in
#"Filtered Rows"

Thanks you

[CNENFRNL]

let
    Today = Date.From(DateTime.LocalNow()),
    #"3m Ago" = Date.AddMonths(Today, -3),
    Dates = Table.FromList(
        List.Dates(#"3m Ago", Duration.Days(Today - #"3m Ago"), #duration(1,0,0,0)),
        Splitter.SplitByNothing(),
        {"Date"}
    )
in
    Dates

[BA_Pete]

Hi @henrycqc ,

Try this in your filter step:

 each [date] >= Date.AddMonths(Date.From(DateTime.LocalNow()), -3)

Pete

[henrycqc]

Thanks

[henrycqc]

HI BA_Pete

How would I use this in a table with multiple columns.

[BA_Pete · Accepted solution]

@henrycqc 

Based on your initial example code:

let
  Source = Dates,
  #"Filtered Rows" = Table.SelectRows(Source, each [dates] >= Date.AddMonths(Date.From(DateTime.LocalNow()), -3))
in
  #"Filtered Rows"

Substitute [dates] here with whatever the date field in your fact table is called.

Pete