Help with dynamic date filter for month and day of month

[Niklerus]

Hi,

Need help to set up a dynamic date filter which is in relation to date when query is run.

I only want rows where date is before the 15th in the previous month.

I got started with below but I can't get the syntax right, I can get it to work for rows before current month but the 15th day part I can't get right:

Table.SelectRows(#"Removed Columns", each [enrolled_date] < (Date.AddMonths(DateTime.FixedLocalNow(),-1),Date.AddDays(DateTime.FixedLocalNow(),-15))

Any help appreciated!

[Einomi]

Hi @Niklerus 

You can try this

let
    Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
    #"Lignes filtrées" = Table.SelectRows(Source, each Date.IsInPreviousMonth([enrolled_date]) and Date.Day([enrolled_date]) < 15)
in
    #"Lignes filtrées"

[Fowmy]

@Niklerus 

= Table.SelectRows(#"Removed Columns", each [enrolled_date] < Date.AddDays(Date.AddMonths(  Date.StartOfMonth( Date.From(DateTime.FixedLocalNow()) ) , -1), 14))

[BA_Pete · Accepted solution]

Hi @Niklerus ,

Try this:

Table.SelectRows(
    #"Removed Columns",
    each let Date.Today = Date.From(DateTime.LocalNow()) in
    [enrolled_date] <
    #date(
        Date.Year(Date.AddMonths(Date.Today, -1)),
        Date.Month(Date.AddMonths(Date.Today, -1)),
        15
    )
)

or more inline:

Table.SelectRows(
    #"Removed Columns",
    each [enrolled_date] <
    #date(
        Date.Year(Date.AddMonths(Date.From(DateTime.LocalNow()), -1)),
        Date.Month(Date.AddMonths(Date.From(DateTime.LocalNow()), -1)),
        15
    )
)

Pete

[Niklerus]

@BA_Pete for the sake of others coming here looking for a solution, the "more inline" version of code doesn't  work, I get: Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?

I pasted both versions as is and the first one works great, the other one does not.
(I wanted to try the second one as it seems a little bit easier to learn/remember the syntax)

[Niklerus]

Found the issue with the second code example, there was an each missing before [enrolled_date]

[BA_Pete]

You're absolutely right, my mistake.

I've updated the inline code so should now work.

Thanks for the heads-up 👍

Pete

[Niklerus]

Beatiful!

Big thank you!