Forum Discussion
M language: input for previous month
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!
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
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
9 Replies
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))))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
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
Thank you very much! Would this work in 01.2022 and bring results for 12.2021?
You can just use this expression
= Date.ToText(Date.AddMonths(Date.From(DateTime.LocalNow()),-1), "M.yyyy")
Pat
Hi IF
Modify the format of Pat's solution:
= Date.ToText(Date.AddMonths(Date.From(DateTime.LocalNow()),-1),"MM.yyyy")
Regards,
Community Support Team _ JingThank you very much! Would this work in 01.2022 and bring result for 12.2021?
Yes, it uses Date.AddMonths function, which is more reliable to get previous month.
