none
Date Filter - NOT in the next / NOT in the previous RRS feed

  • Question

  • I work for a mortgage company and I want to exclude sale dates that have occurred in the last 12 months and also in the next 5 days from my PowerQuery. Unfortunately, the options for Date Filter from PowerQuery only provide the option to filter "Is in the next" or "Is in the Previous." Is there a way to filter through the PowerQuery to exclude those dates? Right now, I load my report and later filter out through conditional formatting by using this code:

    =TODAY()-P1+5 >= 0

    This effectively makes it so that any cell with a date in purple has a date that's before 5 days from now. I understand that this code will not work in a PowerQuery since it uses DAX.

    I also would need to include null cells. I tried converting the null cells to display as 01/01/1900, and then display dates in the next 6 months AND cells with the specific date 01/01/1900, but that didn't work.

    Any help would be much appreciated. Thanks.

    Saturday, February 9, 2019 1:41 AM

Answers

  • Hi

    (dates in dd/mm/yyyy format). According to your rules those highlighted orange should be filtered out (with today = Feb 9th, 2019)

    Assuming data in Table1

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        ChangedType = Table.TransformColumnTypes(Source,
            {{"Date", type date}, {"Amount", type number}}),
    
        // Date.From(DateTime.LocalNow()) same as TODAY() in Excel
        // Calc. TODAY()-5 and store result in var. todayLessFive
        todayLessFive = Date.AddDays(Date.From(DateTime.LocalNow()), -5),
    
        TableFiltered = Table.SelectRows(ChangedType, each
            ([Date] >= todayLessFive) or ([Date] = null)
        )  
    in
        TableFiltered


    Saturday, February 9, 2019 5:52 PM

All replies

  • Hi

    Not 100% clear to me

    1. I'm under the impression you only want sales that have date >= TODAY()-5days or no (null) date, correct?
    2. Also, if we find dates that are i.e. >= TODAY()+10days, should they be included?

    BTW DAX is PowerPivot, Power Query uses M. Power Query M Reference avail. here

    Saturday, February 9, 2019 6:13 AM
  • 1. Yes, I want to exclude any dates that are in the past and 5 days from now. If someone has a foreclosure sale date 5 days from now, there isn’t anything we can do at this point. 2. A foreclosure sale date 10 days in the future would be included along with any cells with no date (ie no sale date scheduled)
    Saturday, February 9, 2019 4:56 PM
  • Hi

    (dates in dd/mm/yyyy format). According to your rules those highlighted orange should be filtered out (with today = Feb 9th, 2019)

    Assuming data in Table1

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        ChangedType = Table.TransformColumnTypes(Source,
            {{"Date", type date}, {"Amount", type number}}),
    
        // Date.From(DateTime.LocalNow()) same as TODAY() in Excel
        // Calc. TODAY()-5 and store result in var. todayLessFive
        todayLessFive = Date.AddDays(Date.From(DateTime.LocalNow()), -5),
    
        TableFiltered = Table.SelectRows(ChangedType, each
            ([Date] >= todayLessFive) or ([Date] = null)
        )  
    in
        TableFiltered


    Saturday, February 9, 2019 5:52 PM
  • Hi Lz_,

    It seems that OP might have abandoned this thread, but now the solution you provided has been marked as a proposed answer, I want to point out a correction that you need to make.

    What the OP asked for (twice). I quote with emphasis:

    "I want to exclude sale dates that have occurred in the last 12 months and also in the next 5 days from my PowerQuery"

    and again (in response to your question)

    "I want to exclude any dates that are in the past and 5 days from now"

    Now, part of the requirement changed from "the last 12 months" to any date in the past", but the exclusion of the next five days has been consistent. The OP goes on to explain why the next 5 days are excluded:

    "If someone has a foreclosure sale date 5 days from now, there isn’t anything we can do at this point"

    Instead, you've excluded 5 days in the past

    So, you just need to change your filter condition, and the solution will be fine :-)

    Lastly, it's not clear to me what happens to today. Today is not in the past, nor is it in the future.

    Monday, February 11, 2019 2:58 PM
  • Hi Colin

    When I suggested the above code I was unsure (maybe my misundertanding of 'foreclosure' at least). Anyway, you seem to have a better understanding of what's expected so feel free to suggest what you think should be done if that case, hopefully this will help me as well

    Then your option could be mark as answer
    Thanks again

    Wednesday, February 13, 2019 1:23 PM
  • You just need to change your filter to remove all dates in the past and the next five days in the future. You are more than capable of doing so. :-)
    Wednesday, February 13, 2019 3:57 PM
  • Sorry, I've been busy. I apologize for not getting back. And thanks for the feedback. The reason that I chose 12 months in the past is that we wouldn't have FCL sale dates from over 12 months ago in the system (we probably wouldn't have anything past 6 months, but I wanted to be safe so that don't miss excluding anything.

    Today should be excluded as well. Thanks.

    Thursday, February 14, 2019 1:11 AM
  • I used the below code successfully!

    = Table.SelectRows(#"Changed Type1", each [FCL Schedule Sale Date] > Date.AddDays(Date.From(DateTime.LocalNow()), 5) or [FCL Schedule Sale Date] = null)

    Thanks!

    Thursday, February 14, 2019 5:03 PM