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
ivath0
Frequent Visitor

Custom Column: End Of Last Month

I want to use Power Query to calculate the number of days between the dates in a Dates column(A) and the end date of last month(B). Eg. Between: (A) 22/6/2020 and (B) 31/01/2021

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

You could take the difference between the start of the current month and column A and subtract a day.

 

Duration.Days(DateTime.Date(Date.StartOfMonth(DateTime.LocalNow())) - [A]) - 1

 

View solution in original post

4 REPLIES 4
Vera_33
Resident Rockstar
Resident Rockstar

Hi @ivath0 

 

You need the data type is Date, paste below M code in Advanced Editor of a blank query

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXJSitWJVjIy0jfTNzIwMgCKGBvqGxiCOIZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"A", type date}, {"B", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each Duration.Days(DateTime.Date(Date.EndOfMonth([B])) - [A]) - 1)
in
#"Added Custom"

AlexisOlson
Super User
Super User

You could take the difference between the start of the current month and column A and subtract a day.

 

Duration.Days(DateTime.Date(Date.StartOfMonth(DateTime.LocalNow())) - [A]) - 1

 

Thank you. That works.

And I tried Date.StartOfMonth(DateTime.LocalNow()) - 1) to give just the last date of last month. The syntax is passed but the column just shows errors. How do I correct the errors? And how do I see an explanation of the errors in Power BI?

@ivath0 

Use Date.AddDays to add or minus number of days.

Date.AddDays(DateTime.Date(Date.StartOfMonth(DateTime.LocalNow())),-1)

021604.jpg

 

You can click the error cell to see the error message.

021603.jpg

Regards,
Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.

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