Net movement between categories

[DickyMoo]

Hi All,

I am tracking movement of members between categories, "Affiliate", "Fellow" etc.

Members can move both ways between these categories, e.g. from Affiliate to Fellow, and from Fellow to Affiliate. (Note, if that sounds odd, its just an illustration).

Does anyone have any ideas on how I can show the net movement?

So if:
10 go Affiliate to Fellow is +10
7 go Fellow to Affiliate is -7
Net Affiliate to Fellow is +3.

Should I set up a table with the combinations of all Prior and Current categories and allocate + or - to each?

Any suggestions?

Thanks
Rich

Note this is a cross post from: https://www.mrexcel.com/forum/power-bi/1098648-net-movement-between-categories.html where I got no replies

[MartinBurman]

did you mean like this? I'm trying to figure this out aswell. will make a separate post aswell

DateCustomer IDCategory

2023-01-01	1	A
2023-01-01	2	A
2023-01-01	3	A
2023-01-01	4	A
2023-01-01	5	A
2023-01-01	6	A
2023-01-01	7	A
2023-01-01	8	A
2023-01-01	9	A
2023-01-01	10	A
2023-01-01	11	A
2023-01-01	12	A
2023-01-01	13	A
2023-01-01	14	B
2023-01-01	15	B
2023-01-01	16	B
2023-01-01	17	B
2023-01-01	18	B
2023-01-01	19	B
2023-01-01	20	B
2023-01-01	21	B
2023-01-01	22	B
2023-01-01	23	B
2023-01-01	24	B
2023-01-01	25	B
2023-01-01	26	B
2023-01-01	27	B
2023-01-01	28	B
2023-01-01	29	B
2023-01-01	30	B
2023-01-01	31	C
2023-01-01	32	C
2023-01-01	33	C
2023-01-01	34	C
2023-01-01	35	C
2023-01-01	36	C
2023-01-01	37	C
2023-01-01	38	C
2023-01-01	39	D
2023-01-01	40	D
2023-01-01	41	D
2023-01-01	42	D
2023-01-01	43	D
2023-01-01	44	D
2023-01-01	45	D
2023-01-01	46	E
2023-01-01	47	F
2023-01-01	48	F
2023-01-01	49	F
2023-08-01	1	B
2023-08-01	2	B
2023-08-01	3	A
2023-08-01	4	A
2023-08-01	5	A
2023-08-01	6	A
2023-08-01	7	A
2023-08-01	8	A
2023-08-01	9	A
2023-08-01	10	A
2023-08-01	11	A
2023-08-01	12	A
2023-08-01	13	A
2023-08-01	14	A
2023-08-01	15	A
2023-08-01	16	A
2023-08-01	17	A
2023-08-01	18	B
2023-08-01	19	B
2023-08-01	20	B
2023-08-01	21	B
2023-08-01	22	B
2023-08-01	23	B
2023-08-01	24	B
2023-08-01	25	B
2023-08-01	26	B
2023-08-01	27	C
2023-08-01	28	C
2023-08-01	29	C
2023-08-01	30	C
2023-08-01	31	A
2023-08-01	32	A
2023-08-01	33	A
2023-08-01	34	B
2023-08-01	35	C
2023-08-01	36	C
2023-08-01	37	C
2023-08-01	38	C
2023-08-01	39	B
2023-08-01	40	B
2023-08-01	41	D
2023-08-01	42	D
2023-08-01	43	D
2023-08-01	44	D
2023-08-01	45	E
2023-08-01	46	A
2023-08-01	47	F
2023-08-01	48	F
2023-08-01	49	F
2023-08-01	n123	A
2023-08-01	N452	A
2023-08-01	N4353	A
2023-08-01	N65454N	B
2023-08-01	NN34	B
2023-08-01	N222	C

[Krishna_Mysore · Accepted solution]

I have created a sample file based on your requirement if I have understood correctly.

Let me know if this helps.

https://drive.google.com/open?id=13iq4oYylYwSwcVE3pOPyawNM9iWK3q4i

[DickyMoo]

Thanks for your reply. It certainly works re the calculation logic.

The issue I have is that I would like to show the movement between specific categories, i.e.:

Prior   Current   Net

Affiliate   Fellow   +2

Member   Fellow   -4

Affiliate   Member   +6

I can't quite work out how I want to handle this, because there will always be an equal and opposite movement for each Prior / Current combination, so the total movement will = 0.

I guess I need to define the Prior / Current category combinations, but not sure how to go about that.

Thanks for taking the time to work up the pbix and replying.

Rich

[Krishna_Mysore]

Please provide sample data showing expected result(An excel file will do) . Without this it is difficult to investigate further.

[DickyMoo]

Hi,

I'm going to close this because I can't get it clear in my head what I'm trying to do, which doesn't make it easy on you!

Thanks

Richard