Forum Discussion

AllanBerces's avatar
AllanBerces
Post Prodigy
1 year ago
Solved

Dynamic Date Filter

Hi good day can anyone help me on my PQ, i want to filter the date before the lates date dynamically. Base from below table i want to filter the date 6/26. by next week or next month the 8/7 if i hav...
  • pankajnamekar25's avatar
    pankajnamekar25
    1 year ago

    Hello AllanBerces 

     

    let
    Source = YourPreviousStep,
    SortedDates = List.Sort(List.Distinct(Source[Date modified]), Order.Ascending),
    LatestDate = List.Last(SortedDates),
    PreviousDate = List.LastN(SortedDates, 2){0},
    FilteredRows = Table.SelectRows(Source, each [Date modified] = PreviousDate)
    in
    FilteredRows

  • mdaatifraza5556's avatar
    1 year ago

    Hi AllanBerces 


    1.   Sort your date column in Descending order ( To sort right click on column and choose descending option)

    2.  Then click on add steps and use thi below M code
          #"Sorted Rows"{1}[Date modified] -------------------------------//#"Sorted Rows" this is the prev steps Name

     

     

     

     

    you get the List 

     

     

     

    Now convert the list into table (you can see upper left side there is option to convert the list to table

     

     

     

    Here is the result

     

     

     

    If this answers your questions, kindly accept it as a solution and give kudos.

  • Dilpreeth_CAT's avatar
    1 year ago

    Step 1: Sort the date column in descending order

    Step 2: Add index column starting from 1

    Step 3: Filter on index column for value = 2