Forum Discussion
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 have a lates data.
Thank you
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
FilteredRowsHi 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 Nameyou 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.
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
6 Replies
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 Nameyou 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.
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
Hi Dilpreeth_CAT mdaatifraza5556 pankajnamekar25 thank you very much for the reply all good and working asi need.
Hello AllanBerces
Try this M code
let
Source = YourPreviousStep,
LatestDate = List.Max(Source[Date modified]),
FilteredRows = Table.SelectRows(Source, each [Date modified] = LatestDate)
in
FilteredRowsthe 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.
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
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
