Average age of machines per year

[vnqt]

Hi, 

I have the following view of the number of machines with the purchased year (date) : 

I would like to create another view of the average age of the machines over year :

For example, the 4 in 2014 : Total months since purchased date until today month / total machines of that year 

The average age should be year.

Here is the sample data : 

Machine	Purchase date
Machine 1	24/04/2014
Machine 2	25/04/2014
Machine 3	26/04/2014
Machine 4	27/04/2014
Machine 5	28/04/2015
Machine 6	29/04/2015
Machine 7	30/04/2015
Machine 8	09/07/2016
Machine 9	10/07/2016
Machine 10	11/07/2016
Machine 11	12/07/2016
Machine 12	13/07/2016
Machine 13	14/07/2016
Machine 14	04/03/2017
Machine 15	08/08/2018
Machine 16	08/08/2018
Machine 17	08/08/2018
Machine 18	08/08/2018
Machine 19	04/05/2019
Machine 20	07/05/2020
Machine 21	07/05/2020
Machine 22	07/05/2020
Machine 23	07/05/2020
Machine 24	30/04/2021
Machine 25	30/04/2021
Machine 26	30/04/2021
Machine 27	06/09/2022
Machine 28	09/06/2023
Machine 29	09/06/2023
Machine 30	09/06/2023
Machine 31	03/07/2024
Machine 32	03/07/2024
Machine 33	03/07/2024
Machine 34	03/07/2024

Many thanks in advance for your help.

[rajendraongole1 · Accepted solution]

Hi @vnqt - Thanks for sharing the data,

first lets create a calculated column ageinmonths as below:

AgeInMonths = DATEDIFF('Agemachine'[Purchase date], TODAY(), MONTH)

TotalAgeInMonths =

SUMX(

    FILTER(

        'Agemachine',

        'Agemachine'[PurchaseYear] = MAX('Agemachine'[PurchaseYear])

    ),

    'Agemachine'[AgeInMonths]

)

One more new column for years

AgeInYears = 'Agemachine'[AgeInMonths] / 12

another column for year from purchase date

PurchaseYear = YEAR('Agemachine'[Purchase date])

Last Lets try to create measure to get the average of age in years as below:

AverageAgeInYears =
AVERAGEX(
FILTER(
'Machines',
'Machines'[PurchaseYear] = MAX('Machines'[PurchaseYear])
),
'Machines'[AgeInYears]
)

Hope it helps

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[vnqt]

Many thanks for your help . 

[rajendraongole1]

Hi @vnqt - Can you please share sample data for reference in excel? it helps to understand the columns and if there is any date field.

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[vnqt]

@rajendraongole1  Hi, thanks for your reply, i just add the sample data