Calculate how many customer in a selected range get back to us

[pjhuongleng]

Hi Everyone, Im stuck with a case that I have a fact_sale table as below

order_date	order_id	product_id	customer_id
10/07/2023	5346842804539	8374540599611	7179117920571
10/07/2023	5346853683515	8374539583803	7179162255675
17/07/2023	5359172845883	8374535618875	7179014635835
17/07/2023	5359152038203	8374536044859	7179014635835
15/08/2023	5454430503227	8374540599611	7179117920571
15/08/2023	5454430503227	8374540042555	717911792057
26/03/2024	5840225632571	8374534930747	7179014635835
26/03/2024	5840225632571	8951011639611	7179014635835
26/03/2024	5840225632571	8374535586107	7179014635835
26/03/2024	5840225632571	8374535717179	7179014635835

Now I want to have a measure that calculate the unique customer in the selected period and how many of them repurchase the after the selected period

for example if I select July 2023 I will have 4 unique customer, so I want to know if any of these 4 customers get back to us after July 2023. For example the table I need is like this

Jul-23	Aug-23	Sep-23	Oct-23	Nov-23	Dec-23	Jan-24
4	1	2	1	1	0	1

If I select July-Sept 2023, it will calculate how many customer in these 3 months purcase with us and how many of them get back to to us from Oct-2023

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[lbendlin]

your sample data doesn't match your expected result. 

I would go with a graphical solution - depending on how many customers you have.

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.