Forum Discussion

ivath0's avatar
ivath0
Frequent Visitor
5 years ago
Solved

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
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    5 years ago

    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

     

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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"