cancel
Showing results for 
Search instead for 
Did you mean: 
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
Memorable Member
Memorable Member

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
Solution Sage
Solution Sage

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
Memorable Member
Memorable Member

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

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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors