Issue with EndOfQuarter

[BM4291]

Hi,

I've built a custom date table in M which I've set to run until the end of the previous quarter. However during the most recent refresh I've come across an issue whereby it's returning an incorrect date which has a knock-on effect on my data.

= Date.AddQuarters(Date.EndOfQuarter(DateTime.LocalNow()), -1) returns 2018-03-30T23:59:59.9999999; I would expect to be seeing 2018-03-31T23:59:59.9999...

Other examples:

= Date.AddQuarters(Date.EndOfQuarter(DateTime.LocalNow()), -2) === 2017-12-30T23:59:59.9999999; expect 2017-12-31

= Date.AddQuarters(Date.EndOfQuarter(DateTime.LocalNow()), -3) === 2017-09-30T23:59:59.9999999; as expected

= Date.AddQuarters(Date.EndOfQuarter(DateTime.LocalNow()), -4) === 2017-06-30T23:59:59.9999999; as expected

If it was to do with daylight savings I'd understand the time being out by an hour, but in two of the above it's out by a day. Any ideas? Is there anything wrong with my syntax/a better way of doing this in M?

For more context, this is how I am using the above:

let
    Source = DateTime.Date(Date.AddQuarters(Date.StartOfQuarter(DateTime.LocalNow()), -9)),
    CreateDateList = List.Dates(Source, Number.From(Date.AddQuarters(Date.EndOfQuarter(DateTime.LocalNow()), -1)) - Number.From(Source), #duration(1,0,0,0)),

[ImkeF · Accepted solution]

Agree, that looks a bit odd (you might consider a bug report here).

You can walk around it using this formula instead:

Date.AddDays(Date.StartOfQuarter(DateTime.LocalNow()), -1)

[BM4291]

Of course the simplest solution works best...!

I will report the issue just in case it is a wider problem but thanks for your time in resolving.