Forum Discussion
Calculating Recency with Auto-Updating Sales Data
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
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!