cancel
Showing results for
Search instead for
Did you mean:
Frequent Visitor

## 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
R3  Between 61 and 120 days
R4  Between 31 and 60 days
R5  Last 30 days```

For frequency,

```2) Frequency (number of purchases in the last 12 months):

F1  1 time
F2  Between 2 and 5 times
F3  Between 6 and 9 times
F4  Between 10 and 11 times
F5  12 times or more```

I am not able to find a solution on how to create two new calculated columns with the above outputs.

Any help on this will be highly appreciated.

Thank you in advance for the support!

1 ACCEPTED SOLUTION
Community Champion

Hi @newbie07

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

3 REPLIES 3
Solution Supplier

Hi @newbie07 ,

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

Frequent Visitor

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

Community Champion

Hi @newbie07

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

## Helpful resources

Announcements

#### Check it Out!

Click here to read more about the December 2021 Updates!

#### Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

#### Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors