cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper IV

## how to get 12 month average forecast till end of next year

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.

Community Support

Hi @alya1 ,

Based on the description, what the desired result? What is the calculation logic？Table 1, table2 and table 3 only have date column.

Based on what values to calculate the forecast average? Can you provide the desired result table data?

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.