How to find the first failure date for service claims

[szub]

Hello,

I am trying to figure out how to determine the first failure date on a piece of equipment and then calcuate the number of days from the first failure date from the equipment invoice date.  I have an asset table with the equipment invoice date and a claim table with the failure date from each claim. The tables have a relationship with by a serial number number ID.  Below are examples of the data and what I am needing to calculate from:

Claim1 - Serial1, Eq Invoice Date 3/3/2022, Failure Date 4/1/2022

Claim10 - Serial1, Eq Invoice Date 3/3/2022, Failure 6/10/2022

I need to identify that 4/1/2022 is the first failure date between all of the claims in the system for Serial1 and that there are 29 days between the two. 

Thank you,

Szub

[tamerj1]

HI @szub 
Please try

Days From First Failure =
SUMX (
    AssetTable,
    DATEDIFF (
        AssetTable[Invoice Date],
        MINX ( RELATEDTABLE ( FailuresTable ), FailuresTable[Failure Date] ),
        DAY
    )
)

[Dhacd]

I have a solution, If you have raw data then copy it from excel and paste it into the reply box. 
I will write the Dax calculated column and share the details with you.

[szub]

Here is some sample data - Thank you!

Claim Table
Serial	Claim #	Failure Date
Serial1	CLAIM1	4/1/2022
SERIAL1	CLAIM10	6/10/2022
SERIAL2	CLAIM20	5/15/2021
SERIAL2	CLAIM25	2/1/2022
SERIAL3	CLAIM27	1/5/2020
SERIAL3	CLAIM35	4/15/2020
SERIAL3	CLAIM50	1/10/2021

Asset Table
Serial	Invoice Date
Serial1	3/3/2022
Serial2	4/30/2020
Serial3	7/18/2019

[v-yiruan-msft · Accepted solution]

Hi  @szub ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want.

You can create two measures as below:

First failure date = 
VAR _selserial =
    SELECTEDVALUE ( 'Claim'[Serial] )
VAR _minfdate =
    CALCULATE (
        MIN ( 'Claim'[Failure Date] ),
        FILTER ( ALLSELECTED ( 'Claim' ), 'Claim'[Serial] = _selserial )
    )
RETURN
    _minfdate

Number of days = 
VAR _curidate =
    SELECTEDVALUE ( 'Asset'[Invoice Date] )
RETURN
    DATEDIFF ( _curidate, [First failure date], DAY )

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards