Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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
Proud to be a Datanaut!
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"
= Table.SelectRows(#"Removed Columns", each [enrolled_date] < Date.AddDays(Date.AddMonths( Date.StartOfMonth( Date.From(DateTime.FixedLocalNow()) ) , -1), 14))
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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
Proud to be a Datanaut!
@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)
Found the issue with the second code example, there was an each missing before [enrolled_date]
You're absolutely right, my mistake.
I've updated the inline code so should now work.
Thanks for the heads-up 👍
Pete
Proud to be a Datanaut!
Beatiful!
Big thank you!