Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
Hi, I would like to calculate depreciation end date based on depreciation start date and depreciation duration in years / months. Any idea how to achieve this in Power Query? Here are sample date:
Using Date.AddMonths or Date.AddYears doesn't work with decimal numbers.
Solved! Go to Solution.
Hi @Krzysztof_Hyla ,
Not sure what you mean by not depending on approximation. If you don't have the actual end date available, how else are you intending to get it without approximating it from the info that you do have available?
You could build the end date from the start date forward which would arguably be more accurate, but you introduce significant complexity for the sake of a day here or there over 3/5/7 years:
let
__depnMonths = Number.RoundDown([depnMonths]),
__depnFrac = [depnMonths] - __depnMonths,
__daysInEndMonth = Date.DaysInMonth(Date.AddMonths([dtDepnStart], __depnMonths)),
__daysToAdd = Number.Round(__daysInEndMonth * __depnFrac)
in
Date.AddDays(
Date.AddMonths([dtDepnStart], __depnMonths),
__daysToAdd
)
Original one-liner versus more complex start-forward build:
Pete
Proud to be a Datanaut!
You can use following formula to minimize rounding error to a fraction of a day.
Date.AddDays(Date.AddMonths([Depreciation Start Date],Number.IntegerDivide([Depreciation Duration in Months],1)),Number.Round(Number.Mod([Depreciation Duration in Months],1)*30,0))
Hi Pete, thanks - I have already tried this method. I just hoped there is a solution that doesn't depend on approximation.
Hi @Krzysztof_Hyla ,
Not sure what you mean by not depending on approximation. If you don't have the actual end date available, how else are you intending to get it without approximating it from the info that you do have available?
You could build the end date from the start date forward which would arguably be more accurate, but you introduce significant complexity for the sake of a day here or there over 3/5/7 years:
let
__depnMonths = Number.RoundDown([depnMonths]),
__depnFrac = [depnMonths] - __depnMonths,
__daysInEndMonth = Date.DaysInMonth(Date.AddMonths([dtDepnStart], __depnMonths)),
__daysToAdd = Number.Round(__daysInEndMonth * __depnFrac)
in
Date.AddDays(
Date.AddMonths([dtDepnStart], __depnMonths),
__daysToAdd
)
Original one-liner versus more complex start-forward build:
Pete
Proud to be a Datanaut!
Hi @Krzysztof_Hyla ,
You could try something like this:
Date.AddDays([Depreciation Start Date], 365.25 * [Depreciation Duration in Years])
You may want to round off the days calculation if there's an error with fractional days, something like this:
Date.AddDays(
[Depreciation Start Date],
Number.Round(365.25 * [Depreciation Duration in Years])
)
Pete
Proud to be a Datanaut!
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
15 | |
10 | |
10 | |
8 | |
7 |