How to count instance changes in a column YoY

[ivandelgra]

Hello I have this dataset:

ID PERSON	CARD TYPE	SUBSCRIPTION YEAR
A	FREE	2018
A	PREMIUM	2019
B	PREMIUM	2018
B	PRO	2019
C	FREE	2018
C	PRO	2019
D	FREE	2018
D	PRO	2018
E	FREE	2018
E	PRO	2018

I would like to know for each year (from 2018 to 2019):

• how many FREE subscribers have changed their plan to PRO subscribers
• how many FREE subscribers have not changed their plan
• how many FREE subscribers have changed their plan to PREMIUM subscribers

I need of course the other combinations, too. 

Please note that someone can change the plan during the same year (as for D and E id person)

Can you help me?

Thank you so much. 

@Greg_Deckler @amitchandak @Mariusz 

[Anonymous]

You can't do this because within a single year there's no way of telling the sequence of events in time.

Unless there's a rule that says it's always true that if there are different card types for a person in a year, FREE always precedes PRO. But what will happen if somebody changes the type many times a year?

Best
D

[ivandelgra]

Hi @Anonymous. Supposing instead that no one can change the plan during the same year, which solution you would find? Thank you

[Anonymous]

That's trivial then.

I would like to know for each year (from 2018 to 2019):

• how many FREE subscribers have changed their plan to PRO subscribers
  • 0
• how many FREE subscribers have not changed their plan
  • just count the people with FREE entries in the table
• how many FREE subscribers have changed their plan to PREMIUM subscribers
  • 0

Best

D

[ivandelgra]

@Anonymous Maybe I explain wrong. I’m sorry for the english. I intended if no one can change MANY TIMES the plan in the same year which would be the solution? That it is, if only one time a year you can change the plan what would you do?
Anyway, as regards to “ You can't do this because within a single year there's no way of telling the sequence of events in time “, if it can be useful, the chrononological order of the subscription/change come from the order of the row: the last subscription (or the last change) is the last row.

[Anonymous]

Sorry, but in DAX THERE IS NO ORDER OF THE ROW. It's not Excel.

Best
D

[ivandelgra]

But @Anonymous there is the possibility to add an index. Isnt't true? I'm reasoning in row context, but i don't know what formula enter in the calculated column.