DAX measure needed to calculate life of parts based on Installed dates

[ali1234]

Not very new to DAX but not an expert in anyway. I have a dataset of part failures (over 2M rows) by system ID. As parts fail, those get replaced by either the same or a different (version of the) part. I need to pick up the failure time and survival time for parts using the Part number key. For failing parts, age is given in the original dataset. However, as I change the timeline slicer, a failed part may not have failed at that time. For example, if date is set to July-2020 the first part in the table below will actually be in a working state. In that case, I'll have to go back in the records and see when the system 101220 had tat PartA installed and calculate the days of survival (for unfailed parts). I need this measure that determines what parts have not failed on a given date (time slicer that will pick months or quarters) and for each given part, calculate the days of survival period (life) for those parts? 

Following is a sample of my dataset:

SystemID	Component Failing	Component Replacing	Date	Part Age (Days)
101220	PartA	PartA	8/2/2020	200
101220	PartB	PartC	8/31/2020	150
101220	PartA	PartD	2/1/2021	183
101330	PartD	PartD	6/30/2021	90
101330	PartA	PartA	9/21/2020	218
101330	PartA	PartD	2/2/2021	134
101330	PartC	PartC	9/2/2021	343

Required result is in the following format and I am looking for a measure for Avg Life that provides me with the values given in that column. PartA on system 101330 was installed on Sep 21, 2021 and therefore has 404 days of age. That's the only 1 surviving PartA. In case of PartD it is average of two parts (installed on 2/1/2021 and 2/2/2021). 

Component ID	Installed	Failed	Avg Age	Avg Life
PartA	2	4	183.75	404
PartB	0	1	150	-
PartC	2	1	343	241
PartD	3	1	90	270

Thank you for your help in guiding me to get the Avg Life measure in DAX (I am using Excel PowerPivots and not Power BI) for this project

[MFelix]

Hi @ali1234 ,

For this you need to have a parts table in order to make the calculations because you have more than one column with the parts in this case I have created a table without relationship and made the following measures:

Failed = CALCULATE(COUNT('Table'[Component Failing]), 'Table'[Component Failing] in VALUES( PArts[Component]))

installed = calculate (COUNT('Table'[Component Replacing]),'Table'[Component Replacing] in VALUES(PArts[Component])) + 0

Average days = CALCULATE(AVERAGE('Table'[Part Age (Days)]),'Table'[Component Failing] in VALUES(PArts[Component]))

However the last column I'm not sure how you are calculating can you explain a little better how you are getting for example the 404 or the 241?

[V-pazhen-msft]

@ali1234 

"PartA on system 101330 was installed on Sep 21, 2021 and therefore has 404 days of age"

I cannot figure out where is the 404 comes from in this sample table, can you explain the calculation please.

Best regards
Paul Zheng

[ali1234]

Sorry for the confusion... 

PartA on system 101330 was installed on Sep21, 2020. I had a typo. See the first table that shows the install date. 440 days would be the time if the part still had not failed until end of October 2021 (in this case the part actually failed on 2/2/201, so it was a bad example. However, if you ignore that failure, then 440 comes from the installation date to end of the selected time period if a part has not failed).

The Avg Life calculation has to be done based on selected calendar time. So, based on the date of 10/30/2021 (if it was selected), Part C average age will be the average of 425 (for the part installed on 8/31/2020) and 58 (age of part installed on 9/2/2021). That'll give us ~242. 

Apologies for putting the table incorrectly in the original email. 

[MFelix · Accepted solution]

Hi @ali1234 ,

On top of the measure I have send add the following measure:

Days = 
VAR TempTable =
    CALCULATETABLE (
        SUMMARIZE (
            'Table',
            'Table'[Component Failing],
            'Table'[Component Replacing],
            'Table'[Date],
            'Table'[SystemID],
            "@DaysInstalled", DATEDIFF ( 'Table'[Date], TODAY (), DAY )
        ),
        'Table'[Component Replacing] IN VALUES ( Parts[Parts] )
    )
RETURN
    AVERAGEX ( TempTable, [@DaysInstalled] )

Check result in attach PBIX file:

[ali1234]

@MFelix ... any thoughts on how to get around this?