Help tracking customer's behavior

[JoshDiaz]

Hi! 

I have a school project and I'm having some issues tracking the amount of customers who are still paying a bill.

I have two tables, one called "Customer" and one called "Receipt", they're connect with the customer's id.

I need the amount of customers who are paying throughout the year.

For instance, let's say that 200 customer paid their bill on January, then, only 180 paid on February, 150 on March, and so on. I have to create a graph out of that.

I was thinking creating a table (or something like that) with all the customers who paid on January 2023. Then check if those IDs show up on February too, march and so on, but I don't know how to do that.

Any hints on how can I achieve that? Or what can I do instead?

Thank you so much in advance! Any help will be appreciated.

[JoshDiaz]

Sorry for the delay Ryan, thank you so much for answering!

The table "customer" looks like this:

Id	enrollment_date	Name
001	01/01/2022	John S.
002	12/01/2022	Kate R.
003	07/01/2022	Taylor P.
004	03/02/2022	Greg D.

Table "receipt"

r_id	c_id	Status	date
R001	001	paid	31/01/2023
R002	002	paid	31/01/2023
R003	003	paid	31/01/2023
R004	001	denied	28/02/2023
R005	002	paid	28/02/2023
R006	003	paid	28/02/2023

I have to track whether the customer paid or not.

First I should use the enrollment date to choose which customers I want to keep track off, so if I choose only customers enrolled on January of 2023, I should keep track of only 3 customers (customer 004 enrolled on Feb.) until the last one stops paying. I was planning on using an slicer to do so, but if you know a best way I'd really appreciate it.

Regarding the graph, for January it should show 3 customers, since the 3 paid. February should show only 2 since customer 001 didn´t pay, and so on until every customer that enrolled on January stops paying. I was planning on use a line graph line with the amount of customers that paid every month. 

Hope that makes it clearer, I really appreciate you taking the time!

[Ashish_Mathur]

Hi,

You may download my solution workbook from here.

Hope this helps.

[ryan_mayu · Accepted solution]

@JoshDiaz 

the year in first table is 2022 and it's 2023 in the second table. should that be the same year?

i update the date and pls see the attachment below

[JoshDiaz]

Thank you so much for your help Ryan! You made it look so easy.

I was going crazy creating new tables and doing a lot of things, I really appreciate your help.

Have a great day 🙂

[ryan_mayu]

you are welcome

[ryan_mayu]

@JoshDiaz 

could you pls provide some sample data? you want to show customer paid in Jan and also in Feb? what if not in Feb or Mar? what do you want to show?