datediff between install date and todays date

[B00m3r]

I have 20,000 assets in an excel spreadsheet, when an asset is installed it generally is tagged with an install date in the excel file. Unfortunately, around 60% of the assets don’t have an install date.  An extract of the excel is pasted below.

Install Date:- When the asset is installed its given an install date. Where there is no date I have dropped in 1/12/2020 a future date to make the asset stand out. [I don’t know what I’m doing I thought that was a good idea at the time]

I’ve added a column for today’s date using Todays Date = TODAY()

To work out the days between install & todays date to give me asset age I’ve used the below “Days BTWN todays date v shipment date” measure.

To give me the AssetAgeYrs I used AssetAgeYrs. = CMDB[Days BTWN todays date v shipment date]/365

My question: How do I obtain the average age of asset in years without including the anomaly
-2.9452055 which is all the assets that didnt have an install date and I suppose from a broader perspective was this the best approach?

Days BTWN todays date v shipment date =

    SWITCH(

           TRUE(),

            CMDB[Todays Date]<CMDB[Shipment Date],-1*DATEDIFF(CMDB[Todays Date],CMDB[Shipment Date],DAY),

             CMDB[Todays Date]>CMDB[Shipment Date],DATEDIFF(CMDB[Shipment Date],CMDB[Todays Date],DAY),

             0

    )

[v-shex-msft · Accepted solution]

Hi @B00m3r,

I'd like to suggest you modify the formula to replace these exception date if you haven't find out a method to deal with them.(e.g. 0)

Days BTWN todays date v shipment date =
IF (
    CMDB[Todays Date] >= CMDB[Shipment Date],
    DATEDIFF ( CMDB[Shipment Date], CMDB[Todays Date], DAY ),
    0
)

Regards,

Xiaoxin Sheng

[B00m3r]

Mr Sheng
Im very grateful for your response which worked perfectly, much appreciated.
Season Grettings.