Forum Discussion
Dynamically Filtering TRUNC(SYSDATE) - 1 Without Breaking Query Folding
If that does not work, it may be the use of Date.IsInPreviousYear()
The try this
Date.Year([DateColumn]) = (Date.Year(DateTime.FixedLocalNow()) - 1)I think this is all you need: When datecolumn is in the last year, it certainly is less than DateTime.FixedLocalNow()
Thank you for the feedback. Date.IsInPreviousYear() does not break folding and I have confirmed it is the second portion of the query. Both DateTime.FixedLocalNow() and Date.From(DateTime(LocalNow()) both break folding. The intent is to filter all results to inlcude all of the prior year and YTD not including today. My data is transactional and updates shortly after each transaction; however, measurements are based on full calendar day perfromance and including the current day would skew the results. Thanks again!
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())
