cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## Adding fractional years or months to date

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:

1 ACCEPTED SOLUTION
Super User

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,
in
)``````

Original one-liner versus more complex start-forward build:

Pete

Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!

4 REPLIES 4
Super User

You can use following formula to minimize rounding error to a fraction of a day.

Helper II

Hi Pete, thanks - I have already tried this method. I just hoped there is a solution that doesn't depend on approximation.

Super User

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,
in
)``````

Original one-liner versus more complex start-forward build:

Pete

Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!

Super User

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

Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors