Calculate Open Customer Defects and Open Customer Issues

[JeevanMallya]

I have a table with REPORTED_CUMULATIVE and CLOSED_CUMULATIVE Column. I want to Generate OPEN_CUSTOMER_DEFECTS which is difference of REPORTED_CUMULATIVE and CLOSED_CUMULATIVE. Also I want to generate a new column OPEN_CUSTOMER_ISSUES  which excludes Defects that are closed ie; Remove the CLOSED_ISSUE_KEY_CUMULATIVE from REPORTED_ISSUE_KEY_CUMULATIVE and present it. Due to limitation, i have included for Year 2022, But the data runs untill current week of 2024.So it should be iterative.

The Input table looks like

PROJECT_KEY	YEAR	WEEK_NUM	REPORTED_CUMULATIVE	REPORTED_ISSUE_KEY_CUMULATIVE	CLOSED_CUMULATIVE	CLOSED_ISSUE_KEY_CUMULATIVE	Index
MD	2022	1	0		0		1
MD	2022	2	0		0		2
MD	2022	3	0		0		3
MD	2022	4	0		0		4
MD	2022	5	0		0		5
MD	2022	6	0		0		6
MD	2022	7	0		0		7
MD	2022	8	0		0		8
MD	2022	9	0		0		9
MD	2022	10	0		0		10
MD	2022	11	0		0		11
MD	2022	12	0		0		12
MD	2022	13	0		0		13
MD	2022	14	0		0		14
MD	2022	15	0		0		15
MD	2022	16	0		0		16
MD	2022	17	0		0		17
MD	2022	18	0		0		18
MD	2022	19	0		0		19
MD	2022	20	0		0		20
MD	2022	21	0		0		21
MD	2022	22	0		0		22
MD	2022	23	0		0		23
MD	2022	24	0		0		24
MD	2022	25	0		0		25
MD	2022	26	0		0		26
MD	2022	27	0		0		27
MD	2022	28	0		0		28
MD	2022	29	0		0		29
MD	2022	30	0		0		30
MD	2022	31	0		0		31
MD	2022	32	0		0		32
MD	2022	33	0		0		33
MD	2022	34	0		0		34
MD	2022	35	0		0		35
MD	2022	36	0		0		36
MD	2022	37	0		0		37
MD	2022	38	0		0		38
MD	2022	39	0		0		39
MD	2022	40	0		0		40
MD	2022	41	0		0		41
MD	2022	42	0		0		42
MD	2022	43	0		0		43
MD	2022	44	0		0		44
MD	2022	45	1	MD-621	0		45
MD	2022	46	4	MD-621,MD-624,MD-630,MD-631	0		46
MD	2022	47	9	MD-621,MD-624,MD-630,MD-631,MD-646,MD-660,MD-674,MD-675,MD-676	0		47
MD	2022	48	20	MD-621,MD-624,MD-630,MD-631,MD-646,MD-660,MD-674,MD-675,MD-676,MD-677,MD-678,MD-679,MD-680,MD-681,MD-682,MD-683,MD-684,MD-687,MD-688,MD-691	0		48
MD	2022	49	34	MD-621,MD-624,MD-630,MD-631,MD-646,MD-660,MD-674,MD-675,MD-676,MD-677,MD-678,MD-679,MD-680,MD-681,MD-682,MD-683,MD-684,MD-687,MD-688,MD-691,MD-699,MD-700,MD-701,MD-702,MD-703,MD-704,MD-705,MD-706,MD-711,MD-712,MD-713,MD-731,MD-735,MD-736	0		49

The Expected Table

PROJECT_KEY	YEAR	WEEK_NUM	REPORTED_CUMULATIVE	REPORTED_ISSUE_KEY_CUMULATIVE	CLOSED_CUMULATIVE	CLOSED_ISSUE_KEY_CUMULATIVE	Index	OPEN_CUSTOMER_DEFECTS	OPEN_CUSTOMER_ISSUES
MD	2022	1	0		0		1	0
MD	2022	2	0		0		2	0
MD	2022	3	0		0		3	0
MD	2022	4	0		0		4	0
MD	2022	5	0		0		5	0
MD	2022	6	0		0		6	0
MD	2022	7	0		0		7	0
MD	2022	8	0		0		8	0
MD	2022	9	0		0		9	0
MD	2022	10	0		0		10	0
MD	2022	11	0		0		11	0
MD	2022	12	0		0		12	0
MD	2022	13	0		0		13	0
MD	2022	14	0		0		14	0
MD	2022	15	0		0		15	0
MD	2022	16	0		0		16	0
MD	2022	17	0		0		17	0
MD	2022	18	0		0		18	0
MD	2022	19	0		0		19	0
MD	2022	20	0		0		20	0
MD	2022	21	0		0		21	0
MD	2022	22	0		0		22	0
MD	2022	23	0		0		23	0
MD	2022	24	0		0		24	0
MD	2022	25	0		0		25	0
MD	2022	26	0		0		26	0
MD	2022	27	0		0		27	0
MD	2022	28	0		0		28	0
MD	2022	29	0		0		29	0
MD	2022	30	0		0		30	0
MD	2022	31	0		0		31	0
MD	2022	32	0		0		32	0
MD	2022	33	0		0		33	0
MD	2022	34	0		0		34	0
MD	2022	35	0		0		35	0
MD	2022	36	0		0		36	0
MD	2022	37	0		0		37	0
MD	2022	38	0		0		38	0
MD	2022	39	0		0		39	0
MD	2022	40	0		0		40	0
MD	2022	41	0		0		41	0
MD	2022	42	0		0		42	0
MD	2022	43	0		0		43	0
MD	2022	44	0		0		44	0
MD	2022	45	1	MD-621	0		45	1	MD-621
MD	2022	46	4	MD-621,MD-624,MD-630,MD-631	0		46	4	MD-621,MD-624,MD-630,MD-631
MD	2022	47	9	MD-621,MD-624,MD-630,MD-631,MD-646,MD-660,MD-674,MD-675,MD-676	0		47	9	MD-621,MD-624,MD-630,MD-631,MD-646,MD-660,MD-674,MD-675,MD-676
MD	2022	48	20	MD-621,MD-624,MD-630,MD-631,MD-646,MD-660,MD-674,MD-675,MD-676,MD-677,MD-678,MD-679,MD-680,MD-681,MD-682,MD-683,MD-684,MD-687,MD-688,MD-691	0		48	20	MD-621,MD-624,MD-630,MD-631,MD-646,MD-660,MD-674,MD-675,MD-676,MD-677,MD-678,MD-679,MD-680,MD-681,MD-682,MD-683,MD-684,MD-687,MD-688,MD-691
MD	2022	49	34	MD-621,MD-624,MD-630,MD-631,MD-646,MD-660,MD-674,MD-675,MD-676,MD-677,MD-678,MD-679,MD-680,MD-681,MD-682,MD-683,MD-684,MD-687,MD-688,MD-691,MD-699,MD-700,MD-701,MD-702,MD-703,MD-704,MD-705,MD-706,MD-711,MD-712,MD-713,MD-731,MD-735,MD-736	0		49	34	MD-621,MD-624,MD-630,MD-631,MD-646,MD-660,MD-674,MD-675,MD-676,MD-677,MD-678,MD-679,MD-680,MD-681,MD-682,MD-683,MD-684,MD-687,MD-688,MD-691,MD-699,MD-700,MD-701,MD-702,MD-703,MD-704,MD-705,MD-706,MD-711,MD-712,MD-713,MD-731,MD-735,MD-736

[JeevanMallya · Accepted solution]

No idea what was not gracious. I had clearly provided the Expected Table in the requirements earlier.

I was able to figure out anf fix by using the power query

// Split REPORTED_ISSUE_KEY_CUMULATIVE into lists
AddReportedList = Table.AddColumn(
#"Removed Columns",
"ReportedList",
each if Text.Length([REPORTED_ISSUE_KEY_CUMULATIVE]) > 0 then
Text.Split([REPORTED_ISSUE_KEY_CUMULATIVE], ",")
else
{}
),

// Split CLOSED_ISSUE_KEY_CUMULATIVE into lists
AddClosedList = Table.AddColumn(
AddReportedList,
"ClosedList",
each if Text.Length([CLOSED_ISSUE_KEY_CUMULATIVE]) > 0 then
Text.Split([CLOSED_ISSUE_KEY_CUMULATIVE], ",")
else
{}
),

// Compute OPEN_ISSUES by removing closed issues from reported issues
AddOpenIssues = Table.AddColumn(
AddClosedList,
"OPEN_ISSUES",
each if List.IsEmpty([ClosedList]) then
[REPORTED_ISSUE_KEY_CUMULATIVE]
else
Text.Combine(List.RemoveMatchingItems([ReportedList], [ClosedList]), ",")
),

// Remove intermediate columns
RemoveIntermediateColumns = Table.RemoveColumns(
AddOpenIssues,
{"ReportedList", "ClosedList"}
)

in
RemoveIntermediateColumns

[bchager]

@JeevanMallya let me remind you of a few things; this is free support and no one on here is obligated to help you. So you should be more clear with your requirements, and more gracious when someone is trying to help you.

[JeevanMallya · Accepted solution]

No idea what was not gracious. I had clearly provided the Expected Table in the requirements earlier.

I was able to figure out anf fix by using the power query

// Split REPORTED_ISSUE_KEY_CUMULATIVE into lists
AddReportedList = Table.AddColumn(
#"Removed Columns",
"ReportedList",
each if Text.Length([REPORTED_ISSUE_KEY_CUMULATIVE]) > 0 then
Text.Split([REPORTED_ISSUE_KEY_CUMULATIVE], ",")
else
{}
),

// Split CLOSED_ISSUE_KEY_CUMULATIVE into lists
AddClosedList = Table.AddColumn(
AddReportedList,
"ClosedList",
each if Text.Length([CLOSED_ISSUE_KEY_CUMULATIVE]) > 0 then
Text.Split([CLOSED_ISSUE_KEY_CUMULATIVE], ",")
else
{}
),

// Compute OPEN_ISSUES by removing closed issues from reported issues
AddOpenIssues = Table.AddColumn(
AddClosedList,
"OPEN_ISSUES",
each if List.IsEmpty([ClosedList]) then
[REPORTED_ISSUE_KEY_CUMULATIVE]
else
Text.Combine(List.RemoveMatchingItems([ReportedList], [ClosedList]), ",")
),

// Remove intermediate columns
RemoveIntermediateColumns = Table.RemoveColumns(
AddOpenIssues,
{"ReportedList", "ClosedList"}
)

in
RemoveIntermediateColumns

[bchager]

@JeevanMallya This should do it. Take a look at rows 6 down in the advanced editor for the Table query.

[JeevanMallya · Accepted solution]

Thank you

[bchager]

@JeevanMallya Does this work for you? .pbix attached.

OPEN_CUSTOMER_DEFECTS = 'Table'[REPORTED_CUMULATIVE] - 'Table'[CLOSED_CUMULATIVE]

OPEN_CUSTOMER_ISSUES = IF('Table'[OPEN_CUSTOMER_DEFECTS] > 0, 'Table'[REPORTED_ISSUE_KEY_CUMULATIVE], BLANK())

[JeevanMallya]

OPEN_CUSTOMER_ISSUE result is not correct. What is expected is 

PROJECT_KEY	YEAR	WEEK_NUM	REPORTED_CUMULATIVE	REPORTED_ISSUE_KEY_CUMULATIVE	CLOSED_CUMULATIVE	CLOSED_ISSUE_KEY_CUMULATIVE	Index	OPEN_CUSTOMER_DEFECTS	OPEN_CUSTOMER_ISSUES
MD	2022	1	0		0		1	0
MD	2022	2	0		0		2	0
MD	2022	3	0		0		3	0
MD	2022	4	0		0		4	0
MD	2022	5	0		0		5	0
MD	2022	6	0		0		6	0
MD	2022	7	0		0		7	0
MD	2022	8	0		0		8	0
MD	2022	9	0		0		9	0
MD	2022	10	0		0		10	0
MD	2022	11	0		0		11	0
MD	2022	12	0		0		12	0
MD	2022	13	0		0		13	0
MD	2022	14	0		0		14	0
MD	2022	15	0		0		15	0
MD	2022	16	0		0		16	0
MD	2022	17	0		0		17	0
MD	2022	18	0		0		18	0
MD	2022	19	0		0		19	0
MD	2022	20	0		0		20	0
MD	2022	21	0		0		21	0
MD	2022	22	0		0		22	0
MD	2022	23	0		0		23	0
MD	2022	24	0		0		24	0
MD	2022	25	0		0		25	0
MD	2022	26	0		0		26	0
MD	2022	27	0		0		27	0
MD	2022	28	0		0		28	0
MD	2022	29	0		0		29	0
MD	2022	30	0		0		30	0
MD	2022	31	0		0		31	0
MD	2022	32	0		0		32	0
MD	2022	33	0		0		33	0
MD	2022	34	0		0		34	0
MD	2022	35	0		0		35	0
MD	2022	36	0		0		36	0
MD	2022	37	0		0		37	0
MD	2022	38	0		0		38	0
MD	2022	39	0		0		39	0
MD	2022	40	0		0		40	0
MD	2022	41	0		0		41	0
MD	2022	42	0		0		42	0
MD	2022	43	0		0		43	0
MD	2022	44	0		0		44	0
MD	2022	45	1	MD-621	0		45	1	MD-621
MD	2022	46	4	MD-621,MD-624,MD-630,MD-631	0		46	4	MD-621,MD-624,MD-630,MD-631
MD	2022	47	9	MD-621,MD-624,MD-630,MD-631,MD-646,MD-660,MD-674,MD-675,MD-676	0		47	9	MD-621,MD-624,MD-630,MD-631,MD-646,MD-660,MD-674,MD-675,MD-676
MD	2022	48	20	MD-621,MD-624,MD-630,MD-631,MD-646,MD-660,MD-674,MD-675,MD-676,MD-677,MD-678,MD-679,MD-680,MD-681,MD-682,MD-683,MD-684,MD-687,MD-688,MD-691	0		48	20	MD-621,MD-624,MD-630,MD-631,MD-646,MD-660,MD-674,MD-675,MD-676,MD-677,MD-678,MD-679,MD-680,MD-681,MD-682,MD-683,MD-684,MD-687,MD-688,MD-691
MD	2022	49	34	MD-621,MD-624,MD-630,MD-631,MD-646,MD-660,MD-674,MD-675,MD-676,MD-677,MD-678,MD-679,MD-680,MD-681,MD-682,MD-683,MD-684,MD-687,MD-688,MD-691,MD-699,MD-700,MD-701,MD-702,MD-703,MD-704,MD-705,MD-706,MD-711,MD-712,MD-713,MD-731,MD-735,MD-736	0		49	34	MD-621,MD-624,MD-630,MD-631,MD-646,MD-660,MD-674,MD-675,MD-676,MD-677,MD-678,MD-679,MD-680,MD-681,MD-682,MD-683,MD-684,MD-687,MD-688,MD-691,MD-699,MD-700,MD-701,MD-702,MD-703,MD-704,MD-705,MD-706,MD-711,MD-712,MD-713,MD-731,MD-735,MD-736
MD	2022	50	38	MD-621,MD-624,MD-630,MD-631,MD-646,MD-660,MD-674,MD-675,MD-676,MD-677,MD-678,MD-679,MD-680,MD-681,MD-682,MD-683,MD-684,MD-687,MD-688,MD-691,MD-699,MD-700,MD-701,MD-702,MD-703,MD-704,MD-705,MD-706,MD-711,MD-712,MD-713,MD-731,MD-735,MD-736,MD-754,MD-802,MD-803,MD-811	1	MD-712	50	37	MD-621,MD-624,MD-630,MD-631,MD-646,MD-660,MD-674,MD-675,MD-676,MD-677,MD-678,MD-679,MD-680,MD-681,MD-682,MD-683,MD-684,MD-687,MD-688,MD-691,MD-699,MD-700,MD-701,MD-702,MD-703,MD-704,MD-705,MD-706,MD-711,MD-713,MD-731,MD-735,MD-736,MD-754,MD-802,MD-803,MD-811
MD	2022	51	46	MD-621,MD-624,MD-630,MD-631,MD-646,MD-660,MD-674,MD-675,MD-676,MD-677,MD-678,MD-679,MD-680,MD-681,MD-682,MD-683,MD-684,MD-687,MD-688,MD-691,MD-699,MD-700,MD-701,MD-702,MD-703,MD-704,MD-705,MD-706,MD-711,MD-712,MD-713,MD-731,MD-735,MD-736,MD-754,MD-802,MD-803,MD-811,MD-835,MD-839,MD-843,MD-844,MD-845,MD-846,MD-848,MD-849	1	MD-712	51	45	MD-621,MD-624,MD-630,MD-631,MD-646,MD-660,MD-674,MD-675,MD-676,MD-677,MD-678,MD-679,MD-680,MD-681,MD-682,MD-683,MD-684,MD-687,MD-688,MD-691,MD-699,MD-700,MD-701,MD-702,MD-703,MD-704,MD-705,MD-706,MD-711,MD-713,MD-731,MD-735,MD-736,MD-754,MD-802,MD-803,MD-811
MD	2022	52	51	MD-621,MD-624,MD-630,MD-631,MD-646,MD-660,MD-674,MD-675,MD-676,MD-677,MD-678,MD-679,MD-680,MD-681,MD-682,MD-683,MD-684,MD-687,MD-688,MD-691,MD-699,MD-700,MD-701,MD-702,MD-703,MD-704,MD-705,MD-706,MD-711,MD-712,MD-713,MD-731,MD-735,MD-736,MD-754,MD-802,MD-803,MD-811,MD-835,MD-839,MD-843,MD-844,MD-845,MD-846,MD-848,MD-849,MD-850,MD-856,MD-863,MD-865,MD-866	2	MD-712,MD-679	52	49	MD-621,MD-624,MD-630,MD-631,MD-646,MD-660,MD-674,MD-675,MD-676,MD-677,MD-678,MD-680,MD-681,MD-682,MD-683,MD-684,MD-687,MD-688,MD-691,MD-699,MD-700,MD-701,MD-702,MD-703,MD-704,MD-705,MD-706,MD-711,MD-712,MD-713,MD-731,MD-735,MD-736,MD-754,MD-802,MD-803,MD-811,MD-835,MD-839,MD-843,MD-844,MD-845,MD-846,MD-848,MD-849,MD-850,MD-856,MD-863,MD-865,MD-866
MD	2022	53	52	MD-621,MD-624,MD-630,MD-631,MD-646,MD-660,MD-674,MD-675,MD-676,MD-677,MD-678,MD-679,MD-680,MD-681,MD-682,MD-683,MD-684,MD-687,MD-688,MD-691,MD-699,MD-700,MD-701,MD-702,MD-703,MD-704,MD-705,MD-706,MD-711,MD-712,MD-713,MD-731,MD-735,MD-736,MD-754,MD-802,MD-803,MD-811,MD-835,MD-839,MD-843,MD-844,MD-845,MD-846,MD-848,MD-849,MD-850,MD-856,MD-863,MD-865,MD-866,MD-879	2	MD-712,MD-679	53	50	MD-621,MD-624,MD-630,MD-631,MD-646,MD-660,MD-674,MD-675,MD-676,MD-677,MD-678,MD-680,MD-681,MD-682,MD-683,MD-684,MD-687,MD-688,MD-691,MD-699,MD-700,MD-701,MD-702,MD-703,MD-704,MD-705,MD-706,MD-711,MD-712,MD-713,MD-731,MD-735,MD-736,MD-754,MD-802,MD-803,MD-811,MD-835,MD-839,MD-843,MD-844,MD-845,MD-846,MD-848,MD-849,MD-850,MD-856,MD-863,MD-865,MD-866
MD	2023	1	52	MD-621,MD-624,MD-630,MD-631,MD-646,MD-660,MD-674,MD-675,MD-676,MD-677,MD-678,MD-679,MD-680,MD-681,MD-682,MD-683,MD-684,MD-687,MD-688,MD-691,MD-699,MD-700,MD-701,MD-702,MD-703,MD-704,MD-705,MD-706,MD-711,MD-712,MD-713,MD-731,MD-735,MD-736,MD-754,MD-802,MD-803,MD-811,MD-835,MD-839,MD-843,MD-844,MD-845,MD-846,MD-848,MD-849,MD-850,MD-856,MD-863,MD-865,MD-866,MD-879	2	MD-712,MD-679	54	50	MD-621,MD-624,MD-630,MD-631,MD-646,MD-660,MD-674,MD-675,MD-676,MD-677,MD-678,MD-680,MD-681,MD-682,MD-683,MD-684,MD-687,MD-688,MD-691,MD-699,MD-700,MD-701,MD-702,MD-703,MD-704,MD-705,MD-706,MD-711,MD-712,MD-713,MD-731,MD-735,MD-736,MD-754,MD-802,MD-803,MD-811,MD-835,MD-839,MD-843,MD-844,MD-845,MD-846,MD-848,MD-849,MD-850,MD-856,MD-863,MD-865,MD-866
MD	2023	2	54	MD-621,MD-624,MD-630,MD-631,MD-646,MD-660,MD-674,MD-675,MD-676,MD-677,MD-678,MD-679,MD-680,MD-681,MD-682,MD-683,MD-684,MD-687,MD-688,MD-691,MD-699,MD-700,MD-701,MD-702,MD-703,MD-704,MD-705,MD-706,MD-711,MD-712,MD-713,MD-731,MD-735,MD-736,MD-754,MD-802,MD-803,MD-811,MD-835,MD-839,MD-843,MD-844,MD-845,MD-846,MD-848,MD-849,MD-850,MD-856,MD-863,MD-865,MD-866,MD-879,MD-898,MD-899	3	MD-712,MD-679,MD-802	55	51	MD-621,MD-624,MD-630,MD-631,MD-646,MD-660,MD-674,MD-675,MD-676,MD-677,MD-678,MD-680,MD-681,MD-682,MD-683,MD-684,MD-687,MD-688,MD-691,MD-699,MD-700,MD-701,MD-702,MD-703,MD-704,MD-705,MD-706,MD-711,MD-712,MD-713,MD-731,MD-735,MD-736,MD-754,MD-803,MD-811,MD-835,MD-839,MD-843,MD-844,MD-845,MD-846,MD-848,MD-849,MD-850,MD-856,MD-863,MD-865,MD-866

Here i want the Actual Defects ID's that are open(Not the sum / count). So what was expected is if a Defect is closed, I want to removed it from Reported and included in OPEN_CUSTOMER_ISSUE Column. Eg: MD-712 was fixed, So from REPORTED_ISSUE_KEY_CUMULATIVE (MD-621,MD-624,MD-630,MD-631,MD-646,MD-660,MD-674,MD-675,MD-676,MD-677,MD-678,MD-679,MD-680,MD-681,MD-682,MD-683,MD-684,MD-687,MD-688,MD-691,MD-699,MD-700,MD-701,MD-702,MD-703,MD-704,MD-705,MD-706,MD-711,MD-712,MD-713,MD-731,MD-735,MD-736,MD-754,MD-802,MD-803,MD-811) - CLOSED_ISSUE_KEY_CUMULATIVE(MD-712) = OPEN_CUSTOMER_ISSUES(MD-621,MD-624,MD-630,MD-631,MD-646,MD-660,MD-674,MD-675,MD-676,MD-677,MD-678,MD-679,MD-680,MD-681,MD-682,MD-683,MD-684,MD-687,MD-688,MD-691,MD-699,MD-700,MD-701,MD-702,MD-703,MD-704,MD-705,MD-706,MD-711,MD-713,MD-731,MD-735,MD-736,MD-754,MD-802,MD-803,MD-811)

I want to exclude CLOSED_ISSUE_KEY_CUMULATIVE from REPORTED_ISSUE_KEY_CUMULATIVE and show them in OPEN_CUSTOMER_ISSUES. If its blank them then REPORTED_ISSUE_KEY_CUMULATIVE will be presented