Skip to main content
cancel
Showing results for 
Search instead 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

Reply
alya1
Helper IV
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  MonthLeave Count  Forecast  
2023Jan5 
2023Feb10 
2023March3 
2023April8 
2023May9 
2023June2 
2023July11 
2023August6 
2023September  7 
2023October10 
2023November5 
2023December6 
2024Jan 6.83
2024Feb 6.99
2024March 6.74
2024April 7.05
2024May 6.97
2024June 6.80
2024July 7.20
2024August 6.89
...... ...

 

 

Thank you!


Raw data background:

table 1 customer list throughout the years uploaded daily

customer id   date   
1114/3/2021
2224/3/2021
3334/3/2021
1114/21/2021
2224/21/2021
3334/21/2021
1114/22/2021
2224/22/2021
3334/22/2021
4444/22/2021
2225/2/2021
3335/2/2021
4445/2/2021
5555/2/2021
11110/1/2023
33310/1/2023
44410/1/2023
55510/1/2023

 table 2 customers who left and dates

customer id   action   date   
111Left4/28/2021
222Left9/9/2023

table 3 customer who joined and dates

customer id   action   date   
111Joined1/1/2021
222Joined4/1/2021
333Joined4/1/2021
444Joined4/22/2021
555Joined5/1/2021
111Joined9/30/2023

Please note that all data is for past 3 years with thousands of customer IDs and with relationship to a calendar table. 



1 REPLY 1
v-jiewu-msft
Community Support
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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors