Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to 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
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
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
Thank 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.
You can just use this expression
= Date.ToText(Date.AddMonths(Date.From(DateTime.LocalNow()),-1), "M.yyyy")
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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?
Yes indeed.
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