Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Super User
Super User

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

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

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
Super User
Super User

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

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

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
Resident Rockstar
Resident Rockstar

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors