M language: input for previous month

[IF]

Hi,

When I try this, I get the result as: 07.2021
YMONTHS = Text.PadStart(Number.ToText(Date.Month(DateTime.Date(DateTime.LocalNow()))),2,"0") & "." & Number.ToText(Date.Year(DateTime.Date(DateTime.LocalNow()))),

If I try this I get: 5.2021
// YMONTHE = Number.ToText(Date.Year(DateTime.Date(DateTime.LocalNow()))-1) & "." & Text.PadStart(Number.ToText(Date.Month(DateTime.Date(DateTime.LocalNow()))),2,"0"),

Assuming that we are in January 2021 and I want to get the previous month as 12.2020. how should I write the string in order to get the correct result for 12.2021 or 12.2022?

thanks in advance!

[watkinnc · Accepted solution]

The reason that your code returns "5.2021" instead of "05.2021" is that you've already concatenated the month to the ".", so your pad of 2 is already met by "5." I would write it like this:

let LastMonth = Date.AddMonths(Date.From(DateTime.LocalNow()), -1), 
Month = Date.Month(LastMonth),

MonthText = if Month > 9 then Text.From(Month) else Text.PadStart(Text.From(Month), 2, "0"),

NewDate = MonthText & "." & Text.From(Date.Year(LastMonth))

in

NewDate

--Nate

[v-jingzhang · Accepted solution]

Hi @IF 

Modify the format of Pat's solution:

= Date.ToText(Date.AddMonths(Date.From(DateTime.LocalNow()),-1),"MM.yyyy")

Regards,
Community Support Team _ Jing

[IF]

Thank you very much! Would this work in 01.2022 and bring result for 12.2021? 

[v-jingzhang]

Yes, it uses Date.AddMonths function, which is more reliable to get previous month.

[mahoneypat]

You can just use this expression

= Date.ToText(Date.AddMonths(Date.From(DateTime.LocalNow()),-1), "M.yyyy")

Pat

[watkinnc · Accepted solution]

The reason that your code returns "5.2021" instead of "05.2021" is that you've already concatenated the month to the ".", so your pad of 2 is already met by "5." I would write it like this:

let LastMonth = Date.AddMonths(Date.From(DateTime.LocalNow()), -1), 
Month = Date.Month(LastMonth),

MonthText = if Month > 9 then Text.From(Month) else Text.PadStart(Text.From(Month), 2, "0"),

NewDate = MonthText & "." & Text.From(Date.Year(LastMonth))

in

NewDate

--Nate

[IF]

Thank you very much! Would this work in 01.2022 and bring results for 12.2021? 

[watkinnc]

Yes indeed.

[Vera_33]

Hi @IF 

Get the date in preivous month first

Number.ToText( Date.Month( Date.AddMonths([Date],-1)))
&"."&
Number.ToText( Date.Year( Date.AddMonths([Date],-1))))

[IF]

Thank you very much! I want to get a result now for 06.2021 as the previous month. Also, it should work in 01.2022 and bring results for 12.2021? Is it possible to have a string that works for the whole year? Regards