none
Filter to Date Before Today RRS feed

  • Question

  • I am trying to add a query step to remove records where date is in current day (or otherwise filter only to records before current day).  Since at the time the query runs, I want to exclude the current (e.g. partial) day of results from the data (if any same-day records exist).

    Is there a "today" or "current date" function?

    I found date.isincurrentday(), but having trouble using it, such as :

    = Table.SelectRows(tableName, each [Date] >= #datetime(2016, 8, 1, 0, 0, 0) and Date.IsInCurrentDay([Date]) is false)

    That certainly doesn't work, but I think it's close.  The goal is to filter to where date is on or after August 1, but before "current day".


    Shawn Keene

    Monday, August 8, 2016 1:48 PM

Answers

  • To filter out the current date:

    = Table.SelectRows(tableName, each not Date.IsInCurrentDay([Date]))

    To filter out dates >= August 1,2016 but excluding the current date:

    = Table.SelectRows(tableName, each [Date] >= #datetime(2016, 8, 1, 0, 0, 0) and not Date.IsInCurrentDay([Date]))

    or

    = Table.SelectRows(tableName, each Date.From([Date]) >= #date(2016, 8, 1) and not Date.IsInCurrentDay([Date]))

    So you were close. :)

    Monday, August 8, 2016 3:31 PM

All replies

  • To filter out the current date:

    = Table.SelectRows(tableName, each not Date.IsInCurrentDay([Date]))

    To filter out dates >= August 1,2016 but excluding the current date:

    = Table.SelectRows(tableName, each [Date] >= #datetime(2016, 8, 1, 0, 0, 0) and not Date.IsInCurrentDay([Date]))

    or

    = Table.SelectRows(tableName, each Date.From([Date]) >= #date(2016, 8, 1) and not Date.IsInCurrentDay([Date]))

    So you were close. :)

    Monday, August 8, 2016 3:31 PM
  • Thanks a billion.

    Shawn Keene

    Monday, August 8, 2016 4:42 PM
  • Do I add this in the query editor as a custom column? Or do I add a custom column to the table in the data view?
    Thursday, November 8, 2018 11:46 AM
  • You can really do either. You could add it as a custom column that evaluates to 'true or false' and then filter by that column.

    In my case I used it in a filter.  For me it was easier to create one filter first by just filtering the table to "is after August 1st".  At that point it created a query step and the formula bar had something like this:

    = Table.SelectRows(tableName, each [Date] >= #datetime(2016, 8, 1, 0, 0, 0) )

    At this point I could have created another query step for filtering to "before today", but there wasn't a built-in option for that.  So I added this rule to the existing query step by inserting the bold part below:

    = Table.SelectRows(tableName, each [Date] >= #datetime(2016, 8, 1, 0, 0, 0) and not Date.IsInCurrentDay([Date]) )

    Now the filter uses both the 'is after august 1' and 'not current day' for that query step. However you cannot edit that query step using the UI anymore.


    Shawn Keene

    Friday, November 9, 2018 9:15 PM