Hi All,
I have created a measures table with below measures:
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])))
There is a calendar table as well (but no relationships to measures table, relationship is set with other tables in background below).
How can I calculate 12 month average forecast for each of the measures till the end of next year (now till Dec 2025) using forecasted values when there are less than 12 actual values?
For example (Customer Leave):
| Year | Month | Leave Count | Forecast |
| 2023 | Jan | 5 | |
| 2023 | Feb | 10 | |
| 2023 | March | 3 | |
| 2023 | April | 8 | |
| 2023 | May | 9 | |
| 2023 | June | 2 | |
| 2023 | July | 11 | |
| 2023 | August | 6 | |
| 2023 | September | 7 | |
| 2023 | October | 10 | |
| 2023 | November | 5 | |
| 2023 | December | 6 | |
| 2024 | Jan | | 6.83 |
| 2024 | Feb | | 6.99 |
| 2024 | March | | 6.74 |
| 2024 | April | | 7.05 |
| 2024 | May | | 6.97 |
| 2024 | June | | 6.80 |
| 2024 | July | | 7.20 |
| 2024 | August | | 6.89 |
| ... | ... | | ... |
Thank you!
Raw data background:
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 calendar table.
