cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
Krzysztof_Hyla
Helper II
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:

Depreciation.jpg

Using Date.AddMonths or Date.AddYears doesn't work with decimal numbers.

 

1 ACCEPTED 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:

BA_Pete_0-1646911097268.png

 

Pete



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

Proud to be a Datanaut!




View solution in original post

4 REPLIES 4
Vijay_A_Verma
Super User
Super User

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))

Krzysztof_Hyla
Helper II
Helper II

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:

BA_Pete_0-1646911097268.png

 

Pete



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

Proud to be a Datanaut!




BA_Pete
Super User
Super User

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



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

Proud to be a Datanaut!




Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

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

Top Solution Authors