Forum Discussion
Query Folding incorrect for Date.IsInPreviousNWeeks
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
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
3 Replies
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
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
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?
