Dynamically Filtering TRUNC(SYSDATE) - 1 Without Breaking Query Folding

[E9067]

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') - 1 AND  TRUNC(SYSDATE) - 1

to filter; however, while attempting the same in Power Query:

each Date.IsInPreviousYear([DateColumn]) and [DateColumn] < Date.From(DateTime.LocalNow())

breaks query folding.

Any ideas on how to filter and prevent breaking query folding would be greatly appreciated.

[Anonymous]

Just break it up into two separate steps.

Of course, the surest way is always to use a query for your variables, so Query1 is the first day of last year, Query2 is Today's date, and your statement would be

each [Date] >= Query1 and < Query2

--Nate

[PwerQueryKees]

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()

[E9067]

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!

[PwerQueryKees]

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???

[PwerQueryKees]

A correct expression would be:

Date.Year([DateColumn]) >= (Date.Year(DateTime.FixedLocalNow()) - 1) and [DateColumn] < Date.From(DateTime.LocalNow())

[PwerQueryKees]

Try DateTime.FixedLocalNow()

each Date.IsInPreviousYear([DateColumn]) and [DateColumn] < Date.From(DateTime.FixedLocalNow())

[lbendlin]

not exactly clear what date range you are trying to achieve.  Can you specify the filters in the Power Query UI?  If you do that then the chances of folding are higher.

[E9067]

All of the prior year and YTD not including the current day is the end goal. Native UI in Power Query does not allow me to remove the current day dynamically. I can easily filter the prior year and YTD as one step and then have tried adding another step to then filter Is Not Latest; however, if there is no data yet reported for the current day it then filters out yesterday. If I am missing something please let me know. Thank you!

[lbendlin]

Is this a Direct Query or Import Mode data source?  If it is import mode I would filter the "incomplete day"  out in the UI.