Count Status change Year over Year

[ivandelgra]

Hello I have this dataset:

INDEX	ID PERSON	CARD TYPE	SUBSCRIPTION YEAR
1	A	FREE	2018
2	B	PREMIUM	2018
3	C	FREE	2018
4	D	PRO	2018
5	A	PREMIUM	2019
6	B	PRO	2019
7	C	PRO	2019
8	E	FREE	2019
9	D	FREE	2020
10	E	PRO	2020

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. 

"Index" column gives the chronological sequence of events.

Can you help me? Maybe can we use DAX in CALCULATED COLUMN and maybe row context thoughts are the right direction, but I don't know what kind of formula enter (I have seen something with earlier... Could be?)

Anyway I have 1,5 million row, so some formulas, if too complicated, could not work.

HELP ME PLEASE!!!

[Greg_Deckler]

Please don't cross post.

Change = 
    VAR __Current = [CARD TYPE]
    VAR __Previous = 
        FILTER(
            'Table',
            [ID PERSON] = EARLIER([ID PERSON]) && 
                [INDEX] < EARLIER([INDEX])
        )
    VAR __LastID = MAXX(__Previous,[INDEX])
    VAR __Last = MAXX(FILTER('Table',[INDEX] = __LastID),[CARD TYPE])
RETURN
    SWITCH(TRUE(),
        ISBLANK(__Last),BLANK(),
        __Current <> __Last, __Last & " to " & __Current,
        BLANK()
    )

[ivandelgra]

Thank you so much @Greg_Deckler, but it actually doesn't guess right the correct result

Moreover, it is strange but even if some row has blank ID Person, the formula returns some result.

How is it possible since I want to understand the same person which plan type he changed to?

Thank you for your patience.

[Greg_Deckler]

Unclear @ivandelgra it seems to work just fine in the attached PBIX so perhaps there is something that you are not telling me. The code looks at the most recent Index for an ID PERSON. If the status is not the same, it logs it correctly. Otherwise, it puts in a blank. So this would handle people changing even within the same year. What are you expected results from the sample data? 

INDEXID PERSONCARD TYPESUBSCRIPTION YEARChange

1	A	FREE	2018
2	B	PREMIUM	2018
3	C	FREE	2018
4	D	PRO	2018
5	A	PREMIUM	2019	FREE to PREMIUM
6	B	PRO	2019	PREMIUM to PRO
7	C	PRO	2019	FREE to PRO
8	E	FREE	2019
9	D	FREE	2020	PRO to FREE
10	E	PRO	2020	FREE to PRO

[ivandelgra]

Here attached the file with my data set after having integrated your formula. 

I have filtered a single ID Person so you can note the error immediately.

Powerbi file:

https://drive.google.com/file/d/1GsWFg27PnacVkptezuTFSSIAVLSQL95E/view?usp=sharing

Dataset source:

https://drive.google.com/file/d/1QpMbP5tNbxN1UsfpQKaNg0RZWTmruQGQ/view?usp=sharing

Thank you @Greg_Deckler .

[Greg_Deckler · Accepted solution]

Minor change required:

Change = 
    VAR __Current = [CARD TYPE]
    VAR __Previous = 
        FILTER(
            'Query1',
            [ID PERSON] = EARLIER([ID PERSON]) && 
                [INDEX] < EARLIER([INDEX])
        )
    VAR __LastID = MAXX(__Previous,[INDEX])
    VAR __Last = IF(ISBLANK(__LastID),BLANK(),MAXX(FILTER('Query1',[INDEX] = __LastID),[CARD TYPE]))
RETURN
    SWITCH(TRUE(),
        ISBLANK(__Last),BLANK(),
        __Current <> __Last, __Last & " to " & __Current,
        BLANK()
    )

I don't typically use an Index starting at 0 because of issues like this.

[Anonymous]

@Greg_Deckler Is this the formula i would need for mine? my response to @amitchandak to confirm exactly what im trying to do

THANK YOU

[ivandelgra]

@Greg_Deckler YOU ARE A GENIOUS!!! WOOOW! 

I have made some minor change, too in order to include the result for people that does not change YoY.

Below you can find the final formula. thank you Greeeeeeeeeeeeg

Change =

VAR __Current = [CARD TYPE]

VAR __Previous =

FILTER(

'Query1';

[ID PERSON] = EARLIER([ID PERSON]) &&

[INDEX] < EARLIER([INDEX])

)

VAR __LastID = MAXX(__Previous;[INDEX])

VAR __Last = IF(ISBLANK(__LastID);BLANK();MAXX(FILTER('Query1';[INDEX] = __LastID);[CARD TYPE]))

RETURN

SWITCH(TRUE();

ISBLANK(__Last);BLANK();

__Current <> __Last || __Current = __Last ; __Last & " to " & __Current;

BLANK()

)

[Greg_Deckler]

You're welcome! Happy to help!