Filter by Category and Date in Previous N Days

[kerya]

Hi all,

I am trying to filter a dataset by a category and previous 365 days in Power Query. As an example, I have 5 classes. Only two of them have to be re-taken yearly. So I want to filter out completions of Class 1 and Class 2 that are over 365 days old at the date of refresh.

I was able to find code for a static date, but I really need the date to be dynamic. Shown underlined here: 

Table.SelectRows(Source, each not List.Contains({"shoes","wallet","tie"},[Category]) or [Transaction Date] <= #date(2017, 1, 1))

Thank you!

[v-stephen-msft]

Hi @kerya ,

Could you tell me if your problem has been solved?
If it is, kindly Accept it as the solution. More people will benefit from it.
Or you are still confused about it, please provide me with more details about your problem.

Best Regards,
Stephen Tao

[Nathaniel_C · Accepted solution]

Hi @kerya 

Here you go!

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

[Nathaniel_C]

Hi @kerya 
Try this:

if Duration.Days(Date.From (DateTime.LocalNow ()) -[Completion Date]) <365 and [Class]=1 or Duration.Days(Date.From (DateTime.LocalNow ()) -[Completion Date]) < 365 and[Class]=2 then 1 else 0

Then filter for 1.

Pictures  to follow.

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

[Nathaniel_C]

Hi @kerya 

Try this: a relative date filter.



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

[kerya]

I really need to be able to do it in Power Query as a cleaning step on my raw data and on items only in those 2 specific categories. Thank you though!