Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Sort column by today's date

Hi 

 

I wish to filter Lease end date column by today's date which is available in table in query editor

 

Please help

 

Need Help

21 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hello-

     

    1) Create a custom column.  Today's date = 

    DateTime.LocalNow()

    2) Create a conditional column where Is today's date = Yes if today's date is equal to your date column and no if it isn't.

     

    Jared 

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank you!

       

      But actually i wish to filter Lease End date column where only filter dates which is upcoming not past date

      See below snap, i wish to filter second column by first column.

      I have already created column the way you suggested.

      hope i clear in my results

       

      please guide

       

      • v-lili6-msft's avatar
        v-lili6-msft
        Community Support
        6 years ago

        hi, Anonymous 

        If Today's Date2 will be updated every day?

        If so, create a conditional custom column that if Lease End date column is after Today's Date2, then 1 else 2.

        Then you could filter this custom column which is 1.

         

        Or use this formula to create a custom column

        if [#"Lease End date -Copy"]>=Date.From(DateTime.LocalNow()) then 1 else 2

        Then filter it by the same logic.

        here is sample pbix file, please try it.

         

         

        Regards,

        Lin

  • Nathaniel_C's avatar
    Nathaniel_C
    Community Champion
    6 years ago

    Anonymous ,

    = Table.SelectRows(#"Changed Type1", each [date] = Date.From(DateTime.LocalNow()))

    You don't need to create a column.
    Let me know if you have any questions.

    If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
    Nathaniel

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago
      Can I have a working file for my reference
    • Nathaniel_C's avatar
      Nathaniel_C
      Community Champion
      6 years ago

      Anonymous ,

      Add this in the Advanced Editor.  
      Let me know if you have any questions.

      If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
      Nathaniel

       

       

       

       

       

      Table.SelectRows equals filter
      #"Changed Type1", is my previous step

      each [date] date is my column name

       

      Date.From(DateTime.LocalNow())) gets today's date, and formats it as a date

       

       

      • Nathaniel_C's avatar
        Nathaniel_C
        Community Champion
        6 years ago

        Anonymous ,

        Trade you for a kudo or two:smileywink:

         

        date 

         

        Above is the PBIX