Calculate percentage based on rolling sum of products vs the part replacements

[leroy773]

All,

I am not sure if it is even possible, but I am trying to track a percentage of parts replacement based on rolling sum of three to four months, and how many parts were replaced during a certain month.

The chart below has serial number which shows up each time a part is replaced for that serial number.  I am trying to sum the number of serial numbers without including duplicates based on date installed.  Such that, in March I would like to i would like for the sum to be 17 products.  Then I would like to find the percentage of components changed in the month of March for that total of 17.

If that does not make it more confusing enough, I would like to for the report to only use the number of part replacements associated with the serial number in the rolling sum.  Such if an item was install in December and in come 8 months later a part was replaced, would not like to ensure that does not show up in the computation.

Not sure if thisis feasible or I communicated it correctly.

Serial Number	Date/Time Opened	Component Level 2	Date Installed
1	11/3/2016 9:20	A	12/4/2015
2	1/22/2016 15:07	A	12/23/2015
2	8/23/2016 9:35	A	12/23/2015
2	8/30/2016 15:48	B	12/23/2015
3	12/8/2015 14:43	B	12/23/2015
3	3/22/2016 13:51	B	12/23/2015
3	4/29/2016 14:21	C	12/23/2015
4	2/16/2016 15:28	A	12/24/2015
5	1/22/2016 15:07	C	1/8/2016
5	4/19/2016 15:53	C	1/8/2016
6	2/24/2016 9:12	A	1/12/2016
7	1/22/2016 15:08	A	1/15/2016
7	2/4/2016 14:17	A	1/15/2016
7	7/7/2016 13:38	A	1/15/2016
8	6/1/2016 3:41	A	3/8/2016
9	8/4/2016 4:57	B	3/10/2016
10	7/22/2016 13:23	B	3/18/2016
11	4/8/2016 6:56	B	3/19/2016
11	6/29/2016 15:14	B	3/19/2016
12	8/30/2016 17:27	C	3/23/2016
13	2/8/2016 17:47	C	3/28/2016
13	6/22/2016 23:15	C	3/28/2016
13	7/26/2016 19:19	D	3/28/2016
13	8/18/2016 21:46	D	3/28/2016
14	9/7/2016 3:19	A	3/30/2016
15	7/20/2016 14:18	A	3/31/2016
16	3/7/2016 13:08	A	3/31/2016
17	8/12/2016 11:59	A	4/13/2016
18	8/29/2016 15:51	A	4/26/2016
19	8/15/2016 0:29	B	5/5/2016
20	6/27/2016 1:49	B	5/14/2016
21	7/4/2016 6:38	B	6/10/2016
22	10/7/2016 5:56	C	7/5/2016
23	8/25/2016 10:08	C	8/11/2016
24	11/14/2016 10:07	C	9/15/2016

[v-huizhn-msft · Accepted solution]

Hi @leroy773,

Based on my understanding, components changed is changed if the Serial Number and Date Installed are same, which the Date/Time Opened is different. You want to calculate the percentage of components changed in each month for the total, right? If it is, I try to reproduce the following solution and get expected result.

First, you should find the changed component. Add an index column, lookup the previous row value and compare them using the formula below. Create a tag column to return whether the component is changed.

In power bi desktop go to query editor, click Add column->Index column, you will get the index column.

Month = Test[Date Installed].[Month]
Lastnumber = LOOKUPVALUE(Test[Serial Number],Test[Index],Test[Index]-1)
Lastopen = LOOKUPVALUE(Test[Date/Time Opened],Test[Index],Test[Index]-1)
Lastinstall = LOOKUPVALUE(Test[Date Installed],Test[Index],Test[Index]-1)
Tag = IF(Test[Serial Number]=Test[Lastnumber]&&Test[Date Installed]=Test[Lastinstall],IF(Test[Date/Time Opened]<>Test[Lastopen],1,0),0)

Then, create measures to calculate the total and percentage.

Changed = CALCULATE(DISTINCTCOUNT(Test[Tag]),ALLEXCEPT(Test,Test[Month]))
Total = CALCULATE(DISTINCTCOUNT(Test[Serial Number]),ALLEXCEPT(Test,Test[Month]))
Percentage = Test[Changed]/Test[Total]

 

Finally, create a table, select the month as axis, the measures as value levels, you will get the expected result.

If you have any question, please feel free to ask.

Best Regards,
Angelia