Forum Discussion

Anders_G's avatar
Anders_G
Helper I
3 years ago
Solved

Help with Date_request in Date.IsInPreviousNMonths

Hi,   I'm trying to manage my data imports with date filters. I want to import X months in the past from the previous year. I've read you can change the date_request in the Date.IsInPreviousNMonths...
Need Help
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    Hi , Anders_G 

    Accoding to your description, you want to filter the previous N months od the last year. Right?

    I mean this , you can put this in "Advanced Editor" in Power Query Editor:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc3BDQAhCETRXjib4ODqQi3G/ttYJJrl+vIzMydJFWEwqBBolQsu3UnkGsLGtpbQGLoNTwrrCZEsHsYPnc3h1SOw2HfS5lPrAw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", (x)=>if Duration.Days(Date.AddDays(Date.From(Date.From(DateTime.LocalNow())),-365)-x[Date]) >=0 and  Duration.Days(Date.AddDays(Date.From(Date.From(DateTime.LocalNow())),-365)-x[Date]) <=60  then 1 else -1          )
in
    #"Added Custom"

    The table is like this:

    Then we can filter the [Custom]=1 then we will meet your need .

     

    Best Regards,

    Aniya Zhang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

     

     

Rickmaurinus's avatar
Rickmaurinus
Helper V
2 years ago

Hi there,

 

The Date.IsInPreviousNMonths function tests whether a date value is within the specified number of months, compared from your current system time. 

 

For your filter to work,  you should change around the 'DateFilter' variable, to a date column that's actually within your query. For instance, if you have a [Sales Date] column you could use: 

Table.SelectRows(Dataset_(LY)_View, each Date.IsInPreviousNMonths([Sales Date], 2))

 

More details here: https://powerquery.how/date-isinpreviousnmonths

 

However, if you want to filter on dates starting from last year, you would need something like: 

 

Table.SelectRows( 
  Dataset_(LY)_View, 
  each [Sales Data] < Date.AddYears( Date.From( DateTime.LocalNow() ), -1 )
    and [Sales Data] >= Date.AddMonths( Date.From( DateTime.LocalNow() ), -14 )
)

  

In that way you filter your data down to the relevant dates. 

 

--------------------------------------------------

@ me in replies or I'll lose your thread

 

Master Power Query M? -> https://powerquery.how

Read in-depth articles? -> BI Gorilla

Youtube Channel: BI Gorilla

 

If this post helps, then please consider accepting it as the solution to help other members find it more quickly.