Forum Discussion

_Ester_'s avatar
_Ester_
Helper I
2 years ago

Calculating Recency with Auto-Updating Sales Data

Hi everyone! I need some assistance with a Power BI project related to calculating "Recency" from our sales data. Recency is a metric that measures the number of days since a customer's last purchas...
Need Help
lbendlin's avatar
lbendlin
Super User
2 years ago

Here's how a sample approach for recency for a single customer.

 

 

_Ester_'s avatar
_Ester_
Helper I
2 years ago

Hi lbendlin,
I tried to replicate your approach below:

lbendlin wrote:

Here's how a sample approach for recency for a single customer.

 

 


I created the measures to calculate Recency as you showed me and I also added the measures for Frequency and Monetary:



It works! However, I can't understand how from here I can visualize the customer migration, for example with a ribbon chart.

I suppose I should somehow assign each customer_id a category based on their current condition, and then count how many customer_ids are in that category, but I can't find a way to do this with the calculated measures.
I tried to create a calculated measure for the RFM segments, but it doesn't work.

Could you guide me?
I updated the PBIX file with these new measures.

Again, thank you!

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    If you still want to avoid using a calculated table the next step is to prepare your independent dimensions  (dates and customers) and then calculate the measures for each of the periods.

     

    Without that the charts may not give you much

     

    • _Ester_'s avatar
      _Ester_
      Helper I
      2 years ago

      Hi lbendlin,
      I still can't understand your solution 😞

      In the meantime, I developed the idea you suggested, adapting it as much as possible to what I need.

      What's changed:

      🔲 I have added measures:
      Recency2, Frequency2, Monetary2, and RFM segments2 which calculate the status of each customer in EVERY period of the year, including periods where no transactions were made.



      🔲 I have added a RFM categories table with the 5 possible categories ("NEW", "FROZEN", etc.) and the "Unique customers per segment" measure which calculates the number of users in each RFM category for each period.

      ✅ What I achieved:
      I can know the size of each RFM segment over time, which can be an indication of how my business is performing.

      Look at the visual below:
      each column represents 100% of customers up to that date.


      ❌ What I didn't achieve:
      A segment may have grown not because of my marketing strategies, but simply because the total number of customers has grown.
      Additionally, I still don't know which segment the customers came from.

      For example:
      If the HABITUAL segment has grown, it is positive if the additional HABITUAL customers were previously TOP AT RISK customers, it is not positive if they were previously TOP customers.

      lbendlin do you think it is possible to start from what I have done and somehow understand the segment each customer belonged to in the previous period?

      I have updated the PBIX with the new measures and visualizations (page: "RFM_Analysis_2" and "Migration")

      Thanks again!