Need to calculate number of days until first failure from unit sale date

[szub]

Hello,

I have two dates that I need to determine the number of days between in a Sales Table and a Claim Table by serial number.  Below is sample data for a Sales and Claim table.  What I need to find is the number of days between the Sale Date on the Sales table and the FIRST Failure Date on the Claim Table if the Claim Type is Standard or Extended.

Sales Table

Serial #	Sale Date
Serial1	1/2/2018
Serial2	2/14/2019
Serial3	9/24/2018
Serial4	12/16/2017
Serial5	3/1/2017
Serial6	4/12/2019
Serial7	11/18/2019

Claim Table

Serial #	Claim #	Claim Type	Failure Date
Serial1	Claim1	Install	2/1/2018
Serial1	Claim2	Standard	1/13/2019
Serial2	Claim3	Startup	3/14/2019
Serial3	Claim4	Standard	3/6/2019
Serial3	Claim5	Standard	10/1/2019
Serial4	Claim6	Startup	3/5/2017
Serial4	Claim7	Standard	2/11/2018
Serial5	Claim8	Standard	2/14/2019
Serial5	Claim9	Extended	7/6/2019

Appreciate any help!

Thanks

[v-yiruan-msft]

Hi @szub，

You can create one measure as below:

bdays =

VAR a =

    MAX ( 'Claim'[Serial #] )

VAR b =

    DATEDIFF (

        CALCULATE (

            MAX ( 'Sales'[Sale Date] ),

            FILTER ( 'Sales', 'Sales'[Serial #] = a )

        ),

        CALCULATE (

            MIN ( 'Claim'[Failure Date] ),

            FILTER (

                'Claim',

                ( 'Claim'[Claim Type] = "Standard"

                    || 'Claim'[Claim Type] = "Extended" )

                    && 'Claim'[Serial #] = a

            )

        ),

        DAY

    )

RETURN

    b

Best Regards

Rena

[az38 · Accepted solution]

Hi @szub 

try a measure

Measure = 
var _firstFailure = calculate(min('Claim Table'[Failure Date]), OR('Claim Table'[Claim Type]="Standard",'Claim Table'[Claim Type]="Extended"))
return 
datediff(selectedvalue('Sales Table'[Sale Date]),_firstFailure,DAY)

[szub]

@az38 That worked!  Thanks

[Greg_Deckler]

So something like:

Column = MINX(RELATEDTABLE('Claim Table'),[Failure Date])