Forum Discussion

ivandelgra's avatar
ivandelgra
Icon for Helper I rankHelper I
6 years ago
Solved

Count Status change Year over Year

Hello I have this dataset:

 

INDEXID PERSONCARD TYPESUBSCRIPTION YEAR
1AFREE2018
2BPREMIUM2018
3CFREE2018
4DPRO2018
5APREMIUM2019
6BPRO2019
7CPRO2019
8EFREE2019
9DFREE2020
10EPRO2020

 

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

Get Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    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.

8 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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's avatar
      ivandelgra
      Icon for Helper I rankHelper I
      6 years ago

      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's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        6 years ago

        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