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

     

     

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