Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Create Calculated column for Recency and Frequency segmentation

I have generated recency and frequency output in csv format from my RFM model. I wanted to segment that output in this manner; 1) Recency R1 Between 181 and 360 days R2 Between 121 and 180 days R...
help needed
Help with DAX
  • VahidDM's avatar
    VahidDM
    5 years ago

    Hi Anonymous 

    Please try these codes to add two columns to your table:

    Frequency_label = 
Var _F ='Table'[frequency]
return
if(_F=1,"F1",if(_F>=2&&_F<=5,"F2",if(_F>=6&&_F<=9,"F3",if(_F>=10&&_F<=11,"F4",if(_F>=12,"F5")))))
    Recency_label = 
Var _F ='Table'[recency]
return
if(_F>=0&&_F<=30,"R5",if(_F>=31&&_F<=60,"R4",if(_F>=61&&_F<=120,"R3",if(_F>=121&&_F<=180,"R2",if(_F>=181&&_F<=360,"R1")))))

     

    Output:

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    Appreciate your Kudos !!

     

     

Tanushree_Kapse's avatar
Tanushree_Kapse
Impactful Individual
5 years ago

Hi Anonymous ,

 

You want to create two seperate columns for frequency and recency?
One with F1, F2 .... other with 1 time,  Between 2 and 5 times...?

Is this what you want?

 

 

Thanks 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Yes, Two seperate columns for frequency and recency?

     

    this is my sample data;

     

    frequency recency
    1               179
    1               158
    1                 61
    2                82
    2                314

     

    Output should be like this,

     

    frequency recency Frequency_label    Recency_label
    1               179          F1                           R2
    1               158          F1                           R2
    1                 61           F1                          R3
    2                82            F2                          R3
    2                314          F2                          R1

    • VahidDM's avatar
      VahidDM
      Super User
      5 years ago

      Hi Anonymous 

      Please try these codes to add two columns to your table:

      Frequency_label = 
Var _F ='Table'[frequency]
return
if(_F=1,"F1",if(_F>=2&&_F<=5,"F2",if(_F>=6&&_F<=9,"F3",if(_F>=10&&_F<=11,"F4",if(_F>=12,"F5")))))
      Recency_label = 
Var _F ='Table'[recency]
return
if(_F>=0&&_F<=30,"R5",if(_F>=31&&_F<=60,"R4",if(_F>=61&&_F<=120,"R3",if(_F>=121&&_F<=180,"R2",if(_F>=181&&_F<=360,"R1")))))

       

      Output:

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      Appreciate your Kudos !!