Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Advanced Editor - Date Filter to include last 2 months, today, and all future or next 5 years

Hello Community,

 

I have been trying to filter my dataset as the parameters above.

 

I can set it up to give me the last 2 months plus the next 5 years, but that excludes today.

 

#"Filtered Rows" = Table.SelectRows(#"Renamed Columns3", each Date.IsInPreviousNMonths([Date], 2) or Date.IsInNextNYears([Date], 5)),

 

I can also set it up where it will give me the last 2 months, and all future from a specific date, which I can selet today, but the problem here is as the future comes, this will be completely wrong since it is a hard coded date.

 

#"Filtered Rows" = Table.SelectRows(#"Changed Type1", each Date.IsInPreviousNMonths([Date], 2) or [Date] >= #date(2020, 4, 17)),

 

Lastly, I have also tried to combine both by doing a manual edit in Advanced editor as below, but this does not work as Date is in Previous and Date is in the Next only work in Basic filtering, and that doesn't allow me to add today.

 

#"Filtered Rows" = Table.SelectRows(#"Changed Type1", each Date.IsInPreviousNMonths([Date], 2) or Date.IsInCurrentDay([Date]) or Date.IsInNextNYears([Date],5)),

 

What I am thinking is that I need to use option 2, but replace the hard coded date with a function, but haven't been able to find a function that will work here.

 

Please help!

Thanks!

  • Anonymous's avatar
    Anonymous
    6 years ago

    Yes, I am aware of that edhans . I am working on modeling.

     

    I figured out a solution that works.

     

    = Table.SelectRows(#"Changed Type1", each Date.IsInPreviousNDays([Date], 60) or [Date] >= Date.From(Date.StartOfMonth(DateTime.LocalNow())))

     

     

3 Replies

  • edhans's avatar
    edhans
    Icon for Community Champion rankCommunity Champion

    Use DateTime.Date(DateTime.LocalNow())

     

    That is the Power Query equivalent to TODAY() in Excel.

    • Anonymous's avatar
      Anonymous
      Not applicable

      Yes, I am aware of that edhans . I am working on modeling.

       

      I figured out a solution that works.

       

      = Table.SelectRows(#"Changed Type1", each Date.IsInPreviousNDays([Date], 60) or [Date] >= Date.From(Date.StartOfMonth(DateTime.LocalNow())))

       

       

      • edhans's avatar
        edhans
        Icon for Community Champion rankCommunity Champion

        Sorry I misunderstood your request. I thought this was your question.

        "What I am thinking is that I need to use option 2, but replace the hard coded date with a function, but haven't been able to find a function that will work here."

        I was replacing the hardcoded date with a function.