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
Anonymous
Not applicable

Rate of Hardware Second Return

Please help a newbie here.

 

i need to create a measure to calculate how many hardware that was return in the last month (lastdate) has also been returned in the last three months (lastdate - 3 months)

 

below will be the formula,

 

count distinct hardware returned in the last month ( that is also returned within the last three months) to be divided to the count of total hardware returned in the last month

 

Thanks in advance,

2 ACCEPTED SOLUTIONS

Hi @Anonymous

 

2. find the serial numbers that has duplicate based on "serial number" Fault Code" 

 

If I get this right, the duplication is based on the combination of both serial number and fault code (failure code in your table). A serial number is not yet considered a duplicate if the fault code is different. Also, you want to know which serial numbers are duplicates? Correct? If so, here's my solution. 

https://drive.google.com/open?id=1oCwqy9p_d6-tOabQ7s4LXyd8H3BoUrII






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

View solution in original post

Hi @Anonymous

 

Understood - have updated the file at the same dropbox link.

 

I created another measure to user in the matrix for this purpose. At least this works, but there may well be better ways to write it.

Serial Numbers Returned Summed Monthly (only if Second Return in Most Recent Month ) =
VAR LatestMonth =
    CALCULATETABLE ( LASTDATE ( 'Calendar'[Month] ), ALLSELECTED ( 'Calendar' ) )
RETURN
    SUMX (
        SUMMARIZE (
            'Defect Found',
            'Calendar'[Month],
            'Defect Found'[Serial Number],
            'Defect Found'[Failure Code]
        ),
        IF (
            NOT ( ISBLANK ( CALCULATE ( [Serial Numbers Second Return], LatestMonth ) ) ),
            [Serial Numbers Returned in Most Recent Month]
        )
    )

Using this measure in a matrix, you can see when the 164 "Second Returns" in March were returned in earlier months.

 

Regards,

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

17 REPLIES 17
Anonymous
Not applicable

Bump,anyone? really need help. Thank you.

Anonymous
Not applicable

or if someone could help me modify the below measure to use LASTDATE instead of MAX, i think it will do the trick, thanks again, im really in urgent need of help, thank you.

 

WithinDate =
var MaxMnth = MAX('Defect Found'[Date of repair])
return
If(DATEDIFF(MaxMnth,'Defect Found'[Date of repair],MONTH)>=-2,1,0)

 

WithinMonth =
var CurMnth = MAX('Defect Found'[Date of repair])
return
If(DATEDIFF(CurMnth,'Defect Found'[Date of repair],MONTH)=0,1,0)

Hi @Anonymous

 

What you're wanting to create is a bit like a 'Returning customers' measure, see here on DAX Patterns.

 

I have uploaded a sample pbix file here showing how I would do it. Explanation below.

 

My interpretation of the 'Repeat Returns' measure you want to create is:

Calculate the count of serial numbers that were both

  1. Returned in the most recent month, i.e. up to 1 month ago; and
  2. Also returned some time in the two months preceding the most recent month, i.e. more than 1 month ago but at most 3 months ago

First, I would recommend you set up a Calendar table with a Date column related to your Date of Repair column, as it makes the date-based calculations much easier.

 

Then I would define measures as follows:

 

Serial Numbers Returned in Most Recent Month = 
CALCULATE ( 
    DISTINCTCOUNT ( 'Defect Found'[Serial Number] ),
    DATESINPERIOD ( 'Calendar'[Date], MAX ( 'Calendar'[Date] ), -1, MONTH )
) 
Serial Numbers Second Return = 
VAR MaxDate =
    MAX ( 'Calendar'[Date] )
VAR LatestMonth =
    DATESINPERIOD ( 'Calendar'[Date], MaxDate, -1, MONTH )
VAR Latest3Months =
    DATESINPERIOD ( 'Calendar'[Date], MaxDate, -3, MONTH )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Defect Found'[Serial Number] ),
        CALCULATETABLE ( VALUES ( 'Defect Found'[Serial Number] ), LatestMonth ),
        EXCEPT ( Latest3Months, LatestMonth )
    )

 

These measures rely on having a valid date table related to your 'Defect Found' table.

 

The first measure returns the count of distinct serial numbers occurring in the latest month.

 

The second measure returns the count of distinct serial numbers occuring in the two months prior to the latest month that also occur in the latest month.

 

Note: Both of these measures could be simplified slightly if you are always filtering on calendar months.

 

The important parts of these measures are the date filters marked in red/green.

  • The green code in both measures returns dates in latest month ending on the max date filtered.
  • The red code returns dates in the two months preceding the latest month.

You could tweak these if you wanted to use different date ranges.

 

Using the data you uploaded, in March I get 144 serial numbers on second return, out of 166, or 87%.

 

Hopefully this helps, but post back if you need more help.

 

Regards,

Owen 🙂


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
Anonymous
Not applicable

Thanks a lot Owen, I will try this but If i am correct, the duplicate criteria here is only serial number, what i am looking for is for both serial number and failure code. I will still try it for now and feedback the results. Thanks Again.

Thanks for the feedback @Anonymous - sorry I missed that detail!

For repetition to be based on failure & serial code combined, just one small change is required in the second measure.

I have updated the uploaded file:

 

Serial Numbers Second Return = 
VAR MaxDate =
    MAX ( 'Calendar'[Date] )
VAR LatestMonth =
    DATESINPERIOD ( 'Calendar'[Date], MaxDate, -1, MONTH )
VAR Latest3Months =
    DATESINPERIOD ( 'Calendar'[Date], MaxDate, -3, MONTH )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Defect Found'[Serial Number] ),
        CALCULATETABLE ( SUMMARIZE ( 'Defect Found', 'Defect Found'[Serial Number], 'Defect Found'[Failure Code] ), LatestMonth ),
        EXCEPT ( Latest3Months, LatestMonth )
    )

The measure still counts distinct Serial Numbers, but the highlighted filter argument ensures that only Serial Number & Failure Code combinations present in the latest month are included when looking back at the earlier months.

 

I note that in your sample dataset, each Serial Number is always paired with the same Failure Code, so the total result for March is still 144.

 

By the way, one assumption I had made is that if a Serial Number & Failure Code appear 2 or more times in the latest month, but not at all in the earlier months, that is not deemed to be a "Second Return". That is, there must have been an occurrence in the earlier months to count it as a return. Is that correct?


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
Anonymous
Not applicable

@OwenAuger, Your solution is really working well so far but your assumption on the returns is actually a bit off. Serial Number and Failure Code that returned on the last month (duplicate on the same month is actually considered a "Return".

 

Hopefully you could provide me with the updated measures for this.

 

By the way, i was also wondering if it is possible to make a table that outputs the details of the return as per below.  Thanks in advance.!!!

 

Serial Number      Fail Code       Jan(1st Month Count)      Feb(2nd Month Count)       Mar(Last Month Count)      Total Occurrence(in 3 Months)

@Anonymous

Thanks for that - I had a play around with how the measure works, and rewrote it a bit so that it looks at serial number / failure code combinations in the latest month and counts those that have 2 or more failures in the last 3 months.

 

Also for summing across months, assuming you want to simply add up the Second Returns in each month, a SUMX measure is probably what you want.

 

So here is the final set of measures:

 

Serial Numbers Returned in Most Recent Month = 
CALCULATE ( 
    DISTINCTCOUNT ( 'Defect Found'[Serial Number] ),
    DATESINPERIOD ( 'Calendar'[Date], MAX ( 'Calendar'[Date] ), -1, MONTH )
) 

Serial Numbers Second Return = 
VAR MaxDate =
    MAX ( 'Calendar'[Date] )
VAR LatestMonth =
    DATESINPERIOD ( 'Calendar'[Date], MaxDate, -1, MONTH )
VAR Latest3Months =
    DATESINPERIOD ( 'Calendar'[Date], MaxDate, -3, MONTH )
RETURN
    CALCULATE (
        CALCULATE (
            DISTINCTCOUNT( 'Defect Found'[Serial Number] ),
            FILTER (
                SUMMARIZE ( 'Defect Found', 'Defect Found'[Serial Number], 'Defect Found'[Failure Code] ),
                CALCULATE ( COUNTROWS ( 'Defect Found' ), Latest3Months ) >= 2
            )
        ),
        LatestMonth
    )


Serial Numbers Second Return Summed Monthly = 
SUMX ( 
    VALUES ( 'Calendar'[Month] ),
    [Serial Numbers Second Return]
)

I saved over my file at the same link.

 

Well, hopefully that gives you something close to what you need even if you need to do some tweaking.

 

Regards,

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
Anonymous
Not applicable

Hi @OwenAuger

 

The result of the summary is not what i was expecting, i was expecting to show a summary of serial numbers returned in the last month with a table showing which month they was returned previously, this is to ensure that the serial numbers are indeed present in the previous three month (range where we guarantee customers that their hardware will not fail), Thanks!

 

regards,

Hi @Anonymous

 

Understood - have updated the file at the same dropbox link.

 

I created another measure to user in the matrix for this purpose. At least this works, but there may well be better ways to write it.

Serial Numbers Returned Summed Monthly (only if Second Return in Most Recent Month ) =
VAR LatestMonth =
    CALCULATETABLE ( LASTDATE ( 'Calendar'[Month] ), ALLSELECTED ( 'Calendar' ) )
RETURN
    SUMX (
        SUMMARIZE (
            'Defect Found',
            'Calendar'[Month],
            'Defect Found'[Serial Number],
            'Defect Found'[Failure Code]
        ),
        IF (
            NOT ( ISBLANK ( CALCULATE ( [Serial Numbers Second Return], LatestMonth ) ) ),
            [Serial Numbers Returned in Most Recent Month]
        )
    )

Using this measure in a matrix, you can see when the 164 "Second Returns" in March were returned in earlier months.

 

Regards,

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
Anonymous
Not applicable

please help me, thank you.

 

my data is here:

https://drive.google.com/open?id=1ke0DkFXEeZR-SQIXF6U0fJ2TZHOCVnyS

 

what i am trying to do,

 

1. compare the "serial number" for rows dated on the last month( in this case, March) to serial numbers from last three months

2. find the serial numbers that has duplicate based on "serial number" Fault Code" 

3. filter only the distinct the "serial numbers" with duplicate for the last month

4. count the distinct serial numbers with duplicates for the last month

5. divide the count of the total serial numbers with duplicates over the total serial numbers of the last month

 

thanks a lot in advance

Hi @Anonymous

 

2. find the serial numbers that has duplicate based on "serial number" Fault Code" 

 

If I get this right, the duplication is based on the combination of both serial number and fault code (failure code in your table). A serial number is not yet considered a duplicate if the fault code is different. Also, you want to know which serial numbers are duplicates? Correct? If so, here's my solution. 

https://drive.google.com/open?id=1oCwqy9p_d6-tOabQ7s4LXyd8H3BoUrII






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.
Anonymous
Not applicable

Hi @danextian

 

Thanks, the solution was working for my requirement at the moment but was having some weird results when i use date and other slicers, anyway @OwenAuger's solution seems to be working perfectly so I will follow up with him on some minor corrections, again, I thank you. I was about to bang my head in the wall prior to both of your answers. 

 

Cheers!

I forgot to mention that I tweaked your data a bit. I copied the row for 17294CT26865026 DI1018 - Tampering Port 11 twice and changed the dates to 11/1/2017 and 12/1/2017 so it's probably the reason why you're seeing extra dates.  That was to check whether the logic I used worked.

 

The logic is if today was 3/28/18, the dates I'd consider in checking for duplicates are 1/1 - 3/31/2018. The same dates if today was 3/1. However it today was 2/1/18, it would be  12/1/2017 up to end of feb.  So depending on the date or repair, you will see varying number of duplicates. 

 

duplicate flag.png






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.
Anonymous
Not applicable

Hi danextian,

 

I am re-opening this question just for continuity because this is a follow up on the same question I have before, our company has recently changed the way we compute second return from:

 

From: 

the sum of duplicate count (from 90 days) in the current month over the sum of total count in the current month

 

To:

the sum of duplicate count (from 90 days) in the current month over the sum of the total count from the last 90 days

 

I have tried multiple tries and it's not giving me 90 days total count the moment I put in the months' column.

 

I tried datesinbetween, earlier, calculate-filter, any help will be greatly appreciated. thanks!

 

by the way I am using the solution you provided back in 2018. 

Anonymous
Not applicable

@danextian I see, let me try it again and keep you posted. Thanks!

 

 

Anonymous
Not applicable

Thanks a lot Danextian, yes that is correct.  I will try this asap and let you know the outcome. Thanks again!

Anonymous
Not applicable

Hello,

 

Please help a newbie here, I am trying to create a measure to calculate the number of duplicates in the last three months of the inputted data dates.

 

Basically what i need is the return rate and the formula is below

 

Duplicate ID + Failure = ID + Failure (two columns) that is present in the last month (lastdate) and also present in the last three months (based on lastdate - 3 months)

Total ID Count = Total number of ID in the last month

 

Return Rate = Duplicate ID + Failure ID Count / Total ID Count

 

Thanks in advance for the help

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.