Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
IF
Post Prodigy
Post Prodigy

M language

Hi

I am using the following and it is working well. How can I get dynamically: "today-3 years" and "today + end of next year".
For example today is 2024-Oct-29. 

today-4 years is 2020-Oct-29

today + end of next year is 2025-Dec-31

Thanks

let
    Today = Date.From(DateTime.LocalNow()),
    TodayFormatted = Date.ToText(Today, "dd.MM.yyyy"),
    TomorrowFormatted = Date.ToText(Date.AddDays(Today, 1), "dd.MM.yyyy")
in
    TomorrowFormatted

 

2 ACCEPTED SOLUTIONS
SamWiseOwl
Super User
Super User

SamWiseOwl_0-1730201905684.pngSamWiseOwl_1-1730202064635.png

= Date.FromText( Text.From( Date.Year(Date.AddYears( Date.From(DateTime.LocalNow()),1))) & "-12-31")


If you are happy with this answer please mark as a solution for others to find !

Kudos are always appreciated! Check out our free Power BI video courses.

View solution in original post

Omid_Motamedise
Super User
Super User

You can add year to specific date by Date.AddYear and also calculate the last date of a year by Date. EndOfYear.

 

So your dates can be achieved by the below formual.

 

Today date => Date.from(DateRime.LocalNow())

Date of next three year=> Date.AddYear(Date.from(DateRime.LocalNow()),3)

 

End of next year => Date.EndOfYear(Date.AddYear(Date.From(DateTime.LocalNow()),1))

 

 

 

 

 

If my answer helped solve your issue, please consider marking it as the accepted solution. It helps others in the community find answers faster—and keeps the community growing stronger!
You can also check out my YouTube channel for tutorials, tips, and real-world solutions in Power Query with the following link
https://youtube.com/@omidbi?si=96Bo-ZsSwOx0Z36h

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

Hi @IF ,

 

Both Omid_Motamedise and SamWiseOwl have provided great solutions. Hope they could help you.

If possible, accept the replies as solutions. This will make it easier for the future people to find the answer quickly.

 

Best Regards,

Stephen Tao

Omid_Motamedise
Super User
Super User

You can add year to specific date by Date.AddYear and also calculate the last date of a year by Date. EndOfYear.

 

So your dates can be achieved by the below formual.

 

Today date => Date.from(DateRime.LocalNow())

Date of next three year=> Date.AddYear(Date.from(DateRime.LocalNow()),3)

 

End of next year => Date.EndOfYear(Date.AddYear(Date.From(DateTime.LocalNow()),1))

 

 

 

 

 

If my answer helped solve your issue, please consider marking it as the accepted solution. It helps others in the community find answers faster—and keeps the community growing stronger!
You can also check out my YouTube channel for tutorials, tips, and real-world solutions in Power Query with the following link
https://youtube.com/@omidbi?si=96Bo-ZsSwOx0Z36h

THank you. I was asking three year backwards. Is it supposed to be:
Date of next three year=> Date.AddYear(Date.from(DateRime.LocalNow()),-3)

SamWiseOwl
Super User
Super User

SamWiseOwl_0-1730201905684.pngSamWiseOwl_1-1730202064635.png

= Date.FromText( Text.From( Date.Year(Date.AddYears( Date.From(DateTime.LocalNow()),1))) & "-12-31")


If you are happy with this answer please mark as a solution for others to find !

Kudos are always appreciated! Check out our free Power BI video courses.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.