Get the last day of the prior year in m query

[officeiq]

I need to add a column to my table that will be the last day of the prior year (e.g. Dec 31, Prior Year/ Dec 31, 2023) so I can build logic that can evaluate the results of this column vs another date column. For example, if Creation Date column is greater than or equal to Prior Date then Y, else N. Then can filter the table by Y or N.

I currently am going in and updating that column manually, but would like a to know if anyone has creted/knows of a way in M Query to generate that Prior Date with a function so i do not have to do it every year manually.

[Fowmy · Accepted solution]

@officeiq 

Suppose you have a date column called Date, add a custom column with this code:

Date.AddYears(Date.EndOfYear([Date]),-1)

[Fowmy · Accepted solution]

@officeiq 

Suppose you have a date column called Date, add a custom column with this code:

Date.AddYears(Date.EndOfYear([Date]),-1)

[officeiq · Accepted solution]

Thanks for your suggestion - I used it with one small modification - I added a column called Today, which uses DateTime.Date(DateTime.LocalNow())) for the column data, so it will automatically update each year. So your updated solution is Date.AddYears(Date.EndOfYear([Today]),-1)) = Thanks