Continuous cumulative count based on discrete dates

[Anonymous]

Hi all,

I have a table with customer IDs, and the date of their first, second, third, and fourth car purchases. (Some customers might not have four purchases, for them the dates would be 2099, data shown below) 

What I need is, for each day, I would like to know how many customers have one car, how many customers have two cars etc. Basically I want to plot the distribution of customers based on how many cars they have against time, and this plot should cover all days from first purchase date until today. For example, first car is purchased on 12th Feb 2019

On this date %100 of my customers have one car. 1 day later on 13th Feb, still %100 of my customers have one car.

On 15th Feb 2019, same customer purchases a car again, so %100 of my customers have two cars. 

On 3rd October, 2019 another customer purchases a car, now I have two customers, one of them have one car and one of them have two cars, and I would like to plot their distribution.

Basically my X axis should be the dates, and on Y axis I would like to have percentage of customers who have one car, two cars and so on. Would appreciate the help so much! Thanks in advance

Customer_ID	First_Purchase	Second_Purchase	Third_Purchase	Fourth_Purchase
7	12 Feb 2019	15 Feb 2019	22 May 2019	25 May 2019
5	3 Oct 2019	1 Jan 2099	1 Jan 2099	1 Jan 2099
3	28 Dec 2019	29 Mar 2020	31 Mar 2023	1 Jan 2099
9	16 Jan 2020	20 Nov 2021	1 Jan 2099	1 Jan 2099
8	30 Sep 2020	9 Aug 2021	18 Dec 2021	1 Jan 2099
4	6 Dec 2020	23 Sep 2021	1 Jan 2099	1 Jan 2099
1	11 Jan 2021	22 Sep 2021	1 Jan 2099	1 Jan 2099
6	14 Jan 2022	22 Dec 2022	22 Feb 2023	1 Jan 2099
2	27 Jun 2023	27 Jun 2023	1 Jan 2099	1 Jan 2099

[Ashish_Mathur · Accepted solution]

Hi,

Please find attached the solution workbook.

Hope this helps.

[Ashish_Mathur]

Hi,

Based on the Table that you have shared, show the expected result in a simple Table format.

[Anonymous]

Hi Ashish

Thanks for your response. Please see below

Date	Number of customers with 1 cars	Number of customers with 2 cars	Number of customers with 3 cars	Number of customers with 4 cars
11 Feb 2019	0	0	0	0
12 Feb 2019	1	0	0	0
13 Feb 2019	1	0	0	0
14 Feb 2019	1	0	0	0
15 Feb 2019	0	1	0	0
.....	0	1	0	0
3 Oct 2019	1	1	0	0
4 Oct 2019	1	1	0	0
....
28 Dec 2019	2	1	0	0

[Ashish_Mathur · Accepted solution]

Hi,

Please find attached the solution workbook.

Hope this helps.

[Anonymous]

Hi Ashish,

Awesome, this works thank you so much!

[Ashish_Mathur]

You are welcome.

[Anonymous]

Hi Ashish,

I have a one final question I hope it is not too much. In my dataset there are customers who has more than 4 purchases, some customers have 10 purchases. So instead of clubbing the customers by 1-2-3-4 purchases, i would like to club them as 1purchase,2purchase,3 purchase, 4 and more than 4 purchases.

Is this something that can be accomodated as well? Basically subsequent purchases after 4 should still stay in the same final bucket (4 and 4+ purchases)

[Ashish_Mathur · Accepted solution]

Hi,

Go To Home > Transform Data.  click on The Categories query and double click on Source.  Make changes there and click on OK.  Click on Close and Apply.

Hope this helps.

[Anonymous]

Great, did this as well and it works. I am also wondering in case there if there is a customer buying two cars on the same day, how would it reflect?

If the customer does not have any car and purchases his first and second cars on the same date, then ideally this customer should be counted as zero cars before the purchase, and 'two cars' on the next day

[Ashish_Mathur]

That is how it should work.  Test it yourself.

[Anonymous]

Hi Ashish, thank you so much for all your support, it is all working, however when I create a relationship with another table (it is a one on one relationship based on the customer ID), the formula is breaking, would you have any idea why? or would I need to share more information

[Ashish_Mathur]

I have no idea.

[Greg_Deckler]

@Anonymous I would start by unpivoting the four columns after your Customer_ID column. Then you will need something like the following: 
Take a look at these two Quick Measures as I think you want something like them.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

Of course, yours will be a variation where you will need to find the "previous value" for the purchase prior to the next purchase:

See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
  __Current - __Previous