Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Custom Date filter in Advanced Editor

I need a custom date filter that includes anything from the past 60 days.  I don't see in "Filtered Columns" that this is an option.  I have put a custom SQL statement into the Advanced Editor, but I...
Need Help
Anonymous's avatar
Anonymous
Not applicable
7 years ago
Have you tried with the Date.IsInPreviousNDays M function? /sample code/ #"Filtered Rows" = Table.SelectRows(#"Changed Type", each Date.IsInPreviousNDays([Date], 60))
  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Thanks, Anonymous.  I will give that a shot.  I did not know the proper syntax to make the change.  I tried something similar to the SQL code, but that was not correct.

    • v-piga-msft's avatar
      v-piga-msft
      Resident Rockstar
      7 years ago

      Hi Anonymous,

       

      For your scenario, you could consider to use query parameter to get the data from past 60 days.

       

      With the Query Parameters, we can define one or multiple parameters to be used in the queries, Data Model and report layers in Power BI Desktop.

       

      For example, to pass the date parameters in PQ Query, you can create a new Query Parameter of Date, then use this date parameter to filter your corresponding date column in the Query. For more details about POWER BI DESKTOP QUERY PARAMETERS, you can refer to this article. 

       

      "Startdate" and "Enddate" are two parameters.

      let
    Source = Sql.Databases(""),
    AdventureWorksDW2012 = Source{[Name="AdventureWorksDW2012"]}[Data],
    dbo_DimDate = AdventureWorksDW2012{[Schema="dbo",Item="DimDate"]}[Data],
    #"Filtered Rows" = Table.SelectRows(dbo_DimDate, each [FullDateAlternateKey] >=Startdate and each [FullDateAlternateKey] <= Enddate )
in
    #"Filtered Rows"

       

      In addition, if it is convenient, could you share some data sample and your desired output so that we could help further on it.

       

      Best Regards,

      Cherry