Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
@officeiq
Suppose you have a date column called Date, add a custom column with this code:
Date.AddYears(Date.EndOfYear([Date]),-1)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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
@officeiq
Suppose you have a date column called Date, add a custom column with this code:
Date.AddYears(Date.EndOfYear([Date]),-1)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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