Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,
Solved! Go to Solution.
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
Proud to be a Super User!
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
Bump,anyone? really need help. Thank you.
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
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.
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 🙂
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?
@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
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
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
Proud to be a Super User!
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.
Proud to be a Super User!
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.
Thanks a lot Danextian, yes that is correct. I will try this asap and let you know the outcome. Thanks again!
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |