Need to calculate if a unit has had a claim within one year of sale

[szub]

Hello,

I am trying to figure out how to identify if a serial number has had a warranty claim within the first year of sale.  Below is some sample data and a measure that someone helped me with, but it is not correctly capturing all of the serial numbers.  Appreciate any assistance.

Sample Serial Data

Sales Serial Nbr	Sale Date
Serial1	10/1/2018
Serial2	2/1/2018
Serial3	4/1/2018
Serial4	6/1/2018
Serial5	9/15/2019
Serial6	10/15/2019

Sample Claim Data:

Claim Serial Nbr	Claim #	Claim Type	Failure Date
Serial1	Claim1	Warranty	12/28/2018
Serial2	Claim2	Warranty	7/1/2019
Serial3	Claim3	Warranty	2/5/2019
Serial3	Claim4	Warranty	8/1/2019
Serial4	Claim5	Startup	4/30/2019

Thank you in advance!

[sturlaws]

Hi @szub 

I think part of the reason why you are not getting the expected results is the somewhat counterintuitive behaviour of DATEDIFF:
https://docs.microsoft.com/en-gb/dax/datediff-function-dax

E.g. DATEDIFF(20191231,20200101,YEAR) return 1, not 0.

You can change to using DAY in your DATEDIFF-statement, and check if the difference is more than 365. 

Cheers,
Sturla

[szub]

@sturlaws ,

Thank you for the feedback.  I had tried changing it to DAY and then it marked every serial number with a 1. I won't be using the measure itself in a visual, but I am putting it into a table visual to validate the results and this is what it is showing.

Thank you

[sturlaws · Accepted solution]

You need to change the if-statement to reflect that when the date difference is less than 366, it has a claim within a year:

had service claims in first year = 
var _curSerial = SELECTEDVALUE('Sample Serial Data'[Sales Serial Nbr])
var _salesdate = CALCULATE(SELECTEDVALUE('Sample Serial Data'[Sale Date]);'Sample Serial Data'[Sales Serial Nbr]=_curSerial)
var _firstClaim = CALCULATE(min('Sample Claim Data'[Failure Date]);'Sample Claim Data'[Claim Serial Nbr]=_curSerial)
var _dif = if(ISBLANK(_firstClaim);366;DATEDIFF(_salesdate;_firstClaim;DAY))
var _result = if(_dif<366;1;0)
return _result

Using this code I get the correct output for the sample data you provided in the original post 

[szub]

@sturlaws ,

That worked!  Thank you so much 🙂

[VasTg]

@szub 

Where do you display the results? as a column in the Seriel data table or in the visual. Do you have any slicer if you want to show it in the visuals?

[szub]

@VasTg ,

It will be in a column in the sales table and used to create a measure showing the percentage of units sold that had a claim in the first year of sale.

Thank you,

[VasTg]

@szub 

Try this..

Warranty_Claimed = 
VAR RELATED_RECS = RELATEDTABLE('Claim Data')
VAR MIN_FAILURE_DATE =  CALCULATE(MIN('Claim Data'[Failure Date]),FILTER(RELATED_RECS,'Claim Data'[Claim Type]="Warranty"))
VAR New_Sale_Date = DATE(YEAR('Serial Data'[Sale Date])+1,MONTH('Serial Data'[Sale Date]),DAY('Serial Data'[Sale Date]))
RETURN IF(ISBLANK(MIN_FAILURE_DATE),0,IF(MIN_FAILURE_DATE<=New_Sale_Date,1,0))

Based on the sample data, Here is the result.

If it helps, mark it as a solution

Kudos are nice too

[szub]

@VasTg ,

Thanks for the information.  When I get to the 3rd line, I am not able to select my sale date field.  It is only showing fields from the sales table that are measures.  The two tables have a relationship by the serial number as a one(sales) to many(claims). What do I need to do differently?

Thank you.