topic Re: M language in Power Query

M language

IF — Tue, 29 Oct 2024 11:26:17 GMT

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

Re: M language

SamWiseOwl — Tue, 29 Oct 2024 11:41:42 GMT

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

Re: M language

Omid_Motamedise — Tue, 29 Oct 2024 11:49:10 GMT

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))

Re: M language

Anonymous — Wed, 30 Oct 2024 02:55:22 GMT

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

Re: M language

IF — Wed, 30 Oct 2024 09:05:04 GMT

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)