Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
szub
Helper III
Helper III

How to find the first failure date for service claims

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

1 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 )

yingyinr_0-1655459241707.png

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
tamerj1
Super User
Super User

HI @szub 
Please try

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

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.

Here is some sample data - Thank you!

Claim Table  
SerialClaim #Failure Date
Serial1CLAIM1          4/1/2022
SERIAL1CLAIM10        6/10/2022
SERIAL2CLAIM20        5/15/2021
SERIAL2CLAIM25         2/1/2022
SERIAL3CLAIM27         1/5/2020
SERIAL3CLAIM35        4/15/2020
SERIAL3CLAIM50        1/10/2021

 

 

Asset Table 
SerialInvoice Date
Serial13/3/2022
Serial24/30/2020
Serial37/18/2019

 

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 )

yingyinr_0-1655459241707.png

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.