topic Re: Count Status change Year over Year in DAX Commands and Tips

Count Status change Year over Year

ivandelgra — Sun, 29 Mar 2020 21:43:52 GMT

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!!!

Re: Count Status change Year over Year

Greg_Deckler — Sun, 29 Mar 2020 22:39:58 GMT

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() )

Re: Count Status change Year over Year

ivandelgra — Sun, 29 Mar 2020 23:22:53 GMT

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.

Re: Count Status change Year over Year

Greg_Deckler — Mon, 30 Mar 2020 04:32:35 GMT

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

Re: Count Status change Year over Year

ivandelgra — Mon, 30 Mar 2020 11:42:00 GMT

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 .

Re: Count Status change Year over Year

Greg_Deckler — Mon, 30 Mar 2020 13:14:54 GMT

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.

Re: Count Status change Year over Year

ivandelgra — Mon, 30 Mar 2020 14:07:20 GMT

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()

)

Re: Count Status change Year over Year

Greg_Deckler — Mon, 30 Mar 2020 15:30:10 GMT

You're welcome! Happy to help!

Re: Count Status change Year over Year

Anonymous — Fri, 19 Mar 2021 17:50:47 GMT

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