Dynamic Date Filter

[AllanBerces]

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 have a lates data.

Thank you

[pankajnamekar25 · Accepted solution]

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 · Accepted solution]

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 · Accepted solution]

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

[AllanBerces]

Hi @Dilpreeth_CAT @mdaatifraza5556 @pankajnamekar25 thank you very much for the reply all good and working asi need.

[mdaatifraza5556 · Accepted solution]

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.

[pankajnamekar25]

Hello @AllanBerces 

Try this M code

let
Source = YourPreviousStep,
LatestDate = List.Max(Source[Date modified]),
FilteredRows = Table.SelectRows(Source, each [Date modified] = LatestDate)
in
FilteredRows

the latest date is 6/26, it keeps only 6/26 rows.

Next week or month, if the latest date changes (e.g., 8/7), it will auto-update.

[AllanBerces]

Hi @pankajnamekar25 thank you for the reply, but the required date i need to filter is not the lates date, the previous date. on my table i have the lates date of 8/7. i need to filter the 6/26 

[pankajnamekar25 · Accepted solution]

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