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 am getting timeout errors when I apply changes or refresh the data.  

So, is there a way to edit the statement below to reflect > 60 days ago instead of > a specific date?

 

[destination_earliest_arrival] >= #datetime(2018, 11, 1, 0, 0, 0)

Need Help

3 Replies

  • 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