Hi All,
I have raw data as below:
table 1 customer list throughout the years uploaded daily
| customer id | date |
| 111 | 4/3/2021 |
| 222 | 4/3/2021 |
| 333 | 4/3/2021 |
| 111 | 4/21/2021 |
| 222 | 4/21/2021 |
| 333 | 4/21/2021 |
| 111 | 4/22/2021 |
| 222 | 4/22/2021 |
| 333 | 4/22/2021 |
| 444 | 4/22/2021 |
| 222 | 5/2/2021 |
| 333 | 5/2/2021 |
| 444 | 5/2/2021 |
| 555 | 5/2/2021 |
| 111 | 10/1/2023 |
| 333 | 10/1/2023 |
| 444 | 10/1/2023 |
| 555 | 10/1/2023 |
table 2 customers who left and dates
| customer id | action | date |
| 111 | Left | 4/28/2021 |
| 222 | Left | 9/9/2023 |
table 3 customer who joined and dates
| customer id | action | date |
| 111 | Joined | 1/1/2021 |
| 222 | Joined | 4/1/2021 |
| 333 | Joined | 4/1/2021 |
| 444 | Joined | 4/22/2021 |
| 555 | Joined | 5/1/2021 |
| 111 | Joined | 9/30/2023 |
Please note that all data is for past 3 years with thousands of customer IDs and with relationship to a date table.
Then, I created a measures table with measures for:
Customer Leave (COUNTA(table 2[date])
Customer Join (COUNTA(table 3[date])
Average Customers (AVERAGEX(VALUES(table 1[date]), CALCULATE(COUNTA(table 1[customer ID])))
Currently, I'm displaying the measures as line/bar charts with dates as x-axis.
How can I create a new data table (not just visual) with columns Month, Year, calculated Join Count, calculated Leave Count, and calculated Average Customers? This is because I want to do forecasting on the columns down the road.
Something like this (random numbers)
| Year | Month | Join Count | Leave Count | Average Customers |
| 2021 | Jan | 3 | 2 | 100 |
| 2021 | Feb | 5 | 6 | 102 |
Thank you!
