cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
IF
Post Prodigy
Post Prodigy

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!

2 ACCEPTED SOLUTIONS
watkinnc
Solution Sage
Solution Sage

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


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

View solution in original post

v-jingzhang
Community Support
Community Support

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

View solution in original post

9 REPLIES 9
v-jingzhang
Community Support
Community Support

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

View solution in original post

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

v-jingzhang
Community Support
Community Support

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

mahoneypat
Super User IV
Super User IV

You can just use this expression

 

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

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


watkinnc
Solution Sage
Solution Sage

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


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

View solution in original post

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

watkinnc
Solution Sage
Solution Sage

Yes indeed.


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
Vera_33
Solution Sage
Solution Sage

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
Post Prodigy
Post Prodigy

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

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Kudoed Authors