Dynamic End Date for Calendar Table

[MAJackson]

Hello,

I am using a calendar table and would like to make the end date to be current year plus 1.  I am using the following formula in the query editor: =Date.EndOfYear(DateTime.Date(DateTime.FixedLocalNow())).  This is giving me the end date for the current year.  I would like the end date for the next calendar year.  How can I adjust this formula to give me that result.

Thanks for your help!

[JarroVGIT]

Something like this?

=DateTime.Date(#datetime(Date.Year(DateTime.FixedLocalNow())+1, 12,31)

Usually I create my data tables in DAX, not PowerQuery but this might be what you are looking for 🙂

[mwegener · Accepted solution]

Date.AddYears(Date.EndOfYear(DateTime.Date(DateTime.FixedLocalNow())),1)

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[MAJackson]

Thank you!  This works perfectly.  I can also adjust the number of years if needed.  I was hving trouble figuring out where to put the number of years, I kept adding "+1" instead of just the "1".

[lbudack]

I've found this method for creating a calendar table to be more flexible. A little bit longer to set up, but it can probably help you here.