Forum Discussion
Continuous cumulative count based on discrete dates
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 |
Hi,
Please find attached the solution workbook.
Hope this helps.
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.
12 Replies
- Greg_DecklerCommunity Champion
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/409365Of 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 - Ashish_MathurSuper User
Hi,
Based on the Table that you have shared, show the expected result in a simple Table format.
- AnonymousNot applicable
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_MathurSuper User