round down difference between two dates

[ajm]

hi all,

I am using an Add Column custom column to hold the number of years between the Start Date and today. My start dates are in column "Start Date". Power query formula is:

=Duration.Days(DateTime.Date(DateTime.LocalNow()) - [Start Date])/365

This gives me the duration in decimal years eg 7.29354768, 0.254677894, 9.65473922. To get this to whole numbers, as a next step i can use Round Down from the Transform menu (Rounding>Round Down) which accurately reduces the decimal numbers to whole numbers.

However, if i try to apply the round down function to the duration day formula mentioned above, i get errors.

this calculation forms part of a larger "if" statement so i need to have only one formula in one step.

any suggestions?

 

[Muhammad_Usman]

If you want to round the result of the formula to the nearest whole number, you can use the `ROUND` function. Here's how you can modify your formula to round the result:

`=ROUND(Duration.Days(DateTime.Date(DateTime.LocalNow()) - [Start Date])/365, 0)`

The `, 0` at the end of the `ROUND` function specifies that you want to round to 0 decimal places, which means rounding to the nearest whole number.

This formula will give you the rounded number of years between the current date and the `[Start Date]`.

 

[ajm]

this is what i ended up with:

Number.RoundDown(Duration.TotalDays(Date.From(DateTime.LocalNow()) - [Start Date])/365.25)+1)*12

we times by 12 to bring the answer back to years for use in Edate formula.

i think Usman's answer above is easier to understand though.

 

[Muhammad_Usman]

Marked Thread as Solved , If your query is solved .

 

[ajm]

fair point.