Query Folding incorrect for Date.IsInPreviousNWeeks

[mike9999]

Hi-

I think that that query folding is incorrect for Date.IsInPreviousNWeeks. Any suggestions on how to fix the m-code on my end, or can anyone confirm this is a bug and how to request this gets fixed?

ex. m-code

= Table.SelectRows(model_MrpReport, each Date.IsInPreviousNWeeks([Source.Date], 52))

produces a native sql query with

where [_].[Source.Date] >= convert(date, '2023-04-09') and [_].[Source.Date] < convert(date, '2024-04-07')

whereas I think it should be <= rather than < on the last convert(date, '2024-04-07')

Mike

[ToddChitt]

The CURRENT week started on 4/7, so PREVIOUS should be anything BEFORE that date, but not including that date. My regional settings say that the week starts on Sunday, as in 4/7. Are your Regional setting correct, and do they match your assumptions?

[OwenAuger · Accepted solution]

Hi @mike9999 

I think this is correct.

The WHERE clause

where [_].[Source.Date] >= convert(date, '2023-04-09') and [_].[Source.Date] < convert(date, '2024-04-07')

is equivalent to

where [_].[Source.Date] >= convert(date, '2023-04-09') and [_].[Source.Date] <= convert(date, '2024-04-06')

since [Source.Date] is of type date.

Since 2023-04-09 is a Sunday and 2024-04-06 is a Saturday, this is a range of complete Sun-Sat weeks by the looks of it.

Does that look right or were you expecting something different?

Regards

[mike9999]

Ah I understand now. I see on the Microsoft docs "Note that this function will return false when passed a value that occurs within the current week."

Intuitively- I would have assumed that Date.IsInPreviousNWeeks would return true for the current week- but really they mean the previous N FULL weeks excluding the week to date. I can solve my problem using another module!

Appreciate the quick response.

Mike