Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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
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"
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?
Use Date.AddDays to add or minus number of days.
Date.AddDays(DateTime.Date(Date.StartOfMonth(DateTime.LocalNow())),-1)
You can click the error cell to see the error message.
Regards,
Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
13 | |
11 |