Date.EndOfYear is a Power Query M function that returns the end of the year, including fractional seconds and preserving time zone information. The function returns the end of the year as a date, datetime, or datetimezone value.
Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365
Syntax
Date.EndOfYear( dateTime as any ) as anyDescription
Date.EndOfYear, returns the last moment of the year based on a given a date, datetime, or datetimezone value (dateTime), preserving any time zone information.
Examples
The Date.EndOfYear function works with date, datetime, and datetimezone values.
Shifting a Date Value
When you apply the Date.EndOfYear function to a date value, it shifts the date to the end of the year:
// Output: #date( 2024, 12, 31 )
Date.EndOfYear( #date( 2024, 5, 15 ) )This expression returns the last day of the year, December 31, 2024.
To visualize this, imagine a table with a date column containing multiple date values. You can shift each of these to the end of their respective years by using Date.EndOfYear.
Shifting a DateTime Value
The function also supports datetime values. To shift a datetime value of May 15th, 2024, 12:00 AM to the end of the year, you can use:
// Output: #datetime( 2024, 12, 31, 23, 59, 59.9999999 )
Date.EndOfYear( #datetime( 2024, 5, 15, 0, 0, 0 ) )Not only does this shift the date to the end of the year, but it also moves the time from 12:00 AM to 11:59 PM on December 31, 2024. Here are some example datetime values to illustrate this:
Shifting a DateTimeZone Value
Similarly, applying the logic to a datetimezone value shifts the date to the end of the year and the time to the end of the day, while keeping the timezone information intact:
// Output: #datetimezone( 2024, 12, 31, 23, 59, 59.9999999, 1, 0 )
Date.EndOfYear( #datetimezone( 2024, 5, 15, 0, 0, 0, 1, 0 ) )Related articles
Learn more about Date.EndOfYear in the following articles:
- Create Date Table or Calendar in Power Query M
Learn how to create a dynamic calendar table in Power Query’s M language. Build your custom columns and claim your free Date Table Script. » Read more
Related functions
Other functions related to Date.EndOfYear are:
2023-2024 © BI Gorilla. All rights are reserved. Information from Microsoft docs is property of Microsoft Corp. | Privacy Policy