Filter date until this month

[Anonymous]

Hi,

I have Leave Data from dynamics.

So I have 2 types of leave, 1 is Leave Credit, and 1 is Annual Leave.

the Leave Credit data is already loaded until the end of the year. Example:

we have 2016 and 2017 data, for 2016 we just put it as leftover in the first January.

For Now I put the filter (in edit queries) date is in year this year or leave type not equals leave credit.

So if it's not November yet the data for Leave credit only shown until October.

But if the years in 2018, in month March, the leave credit only will be shown at March 2017 - March 2018.

There will be no data for January, and February 2017. What I need is the filter all the data until this month.

But using filter in Edit Query because I already create some calculation there.

I hope you can understand my question and example.

Thanks,

[Anonymous]

Up, hoping someone can help

[Anonymous · Accepted solution]

Already found the answer!

= Table.SelectRows(#"Name of Previous Step", each [pafo_EstimatedEndDate] <= DateTime.LocalNow())

 Just want to share if someone needs it 🙂

Thanks!

[Anonymous]

Hi conniedevina,

could you please share the workbook 🙂

Thank you in advance!

Best Regards,

Anastasiia Lagunova

[Anonymous]

Hi @Anonymous,

Please check this link for the workbook and data source (Excel).

Thanks,
Regards,
Connie

[v-chuncz-msft]

@Anonymous,

You may select "Custom Filter..." and do research into The Advanced Editor.

    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each Date.IsInPreviousNMonths([Date], 12) or Date.IsInCurrentMonth([Date]))

[Anonymous]

but if you use

Date.IsInPreviousNMonths([Date], 12) 

 It will be only take data for last year right?

Example the data already from January 2017 - March 2018.

Today month is February 2018, it will only take data from February 2017 - February 2018 right?

What I need is the data from January 2017 - February 2018 (if the example today is february).