Forum Discussion

henrycqc's avatar
henrycqc
Icon for Helper II rankHelper II
4 years ago
Solved

Include only Latest 3 months of data

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

 

Need Help
  • BA_Pete's avatar
    BA_Pete
    4 years ago

    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

5 Replies

  • BA_Pete's avatar
    BA_Pete
    Icon for Super User rankSuper User
    4 years ago

    Hi henrycqc ,

     

    Try this in your filter step:

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

     

    Pete

    • henrycqc's avatar
      henrycqc
      Icon for Helper II rankHelper II
      4 years ago

      HI BA_Pete

       

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

      • BA_Pete's avatar
        BA_Pete
        Icon for Super User rankSuper User
        4 years ago

        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

  • CNENFRNL's avatar
    CNENFRNL
    Icon for Community Champion rankCommunity Champion
    4 years ago
    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