topic Re: How to count instance changes in a column YoY in DAX Commands and Tips

How to count instance changes in a column YoY

ivandelgra — Sat, 28 Mar 2020 21:42:57 GMT

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

Re: How to count instance changes in a column YoY

Anonymous — Sat, 28 Mar 2020 23:00:07 GMT

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

Re: How to count instance changes in a column YoY

ivandelgra — Sat, 28 Mar 2020 23:15:19 GMT

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

Re: How to count instance changes in a column YoY

Anonymous — Sat, 28 Mar 2020 23:19:13 GMT

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

Re: How to count instance changes in a column YoY

ivandelgra — Sat, 28 Mar 2020 23:39:11 GMT

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.

Re: How to count instance changes in a column YoY

Anonymous — Sun, 29 Mar 2020 01:31:31 GMT

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

Best
D

Re: How to count instance changes in a column YoY

ivandelgra — Sun, 29 Mar 2020 13:46:25 GMT

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.