Forum Discussion
Dynamically Filtering TRUNC(SYSDATE) - 1 Without Breaking Query Folding
I have read several questions related to filtering dates without breaking query folding, but none seem to help with my issue. In Oracle I would write: DateColumn BETWEEN TRUNC(SYSDATE, 'YYYY')...
You're welcome. Sorry I can not be of more assistance on the query folding question.
But you say
The intent is to filter all results to inlcude all of the prior year and YTD not including today.
That is what the SQL expression does.
But the PowerQuery expression you are using doesn't do that.
Date.IsInPreviousYear([DateColumn]) and [DateColumn] < Date.From(DateTime.LocalNow())Lets say DateColumn is today: 22-dec-2024
Then the IsInPrevious year is false and by the use of the AND, the whole expression becomes false. So all YTD dates in 2024 will not be returned by the Table.SelectRows()
Am I mistaken???
A correct expression would be:
Date.Year([DateColumn]) >= (Date.Year(DateTime.FixedLocalNow()) - 1) and [DateColumn] < Date.From(DateTime.LocalNow())