Age of inventory for every year

[dnl_kln]

Hello folks,

I would like to calculate the average age of, let's say cars in our inventory.

I have a purchase date and a sell-date for every car, so calculating the age of every year is kind of simple.

What I want to achieve is the average age of all of the cars calculated for every year separately - and just if the car is still active and not already sold. Example:

- Car 1 is purchased on 01.01.2019 and sold on 31.12.2022

- Car 2 is purchased on 01.01.2020 and sold on 31.12.2022

The Average age of both cars differs from year 2019 to 2022:

2019: Average age = 1 year

2020: Average age = 1.5 years

2021: Average age= 2.5 years

2022: Average age= 3.5 years

Is this achievable?

[vinieme12]

As below

=AVG(AGE(solddate,purchasedate))

[dnl_kln]

Doesn't work - see the attached file. The age of the car should grow, as it becomes older through the years. combined with YEAR from the MasterCalendar it's always the same.

It should be (whether calculated with datedif or age) 

in 2018: ~ 0,3 years

in 2019: ~ 1.3 years

in 2020: ~2.3 years

in 2021: ~3.3 years

in 2022: ~ 4.3 years

Any other ideas?

[Or]

You should be able to calculate this using something along the lines of @vinieme12 's formula, but adjusting for the lower between the sold date and year end using: RangeMin(MakeDate(YearField,12,31),SoldDate)

[rubenmarin]

Hi, it may be something like: Avg(Aggr(If(Buy<=MakeDate(Year,12,31) and Sell>=MakeDate(Year,12,31), MakeDate(Year,12,31)-Buy)
,Car,Year))

Maybe it needs to adjust Makedate to the sold date... unless you want the age at the end of each year, where it can work as it is.