Forecast Line - Spread over 12 month period

[JJT1990]

Hi, I'm having a bit of an issue figuring out how to get my forecast line (green) to spread the data over the 12 month period. 

I have the following measure for my actual line (dark blue)

MEASURE - Cumulative FTE Actual = 
CALCULATE([MEASURE - FTE - Finance Hours to FTE Monthly],
Filter(
    ALLSELECTED(Finance_Actuals_FACT),
'Finance_Actuals_FACT'[Date] <= MAX(Finance_Actuals_FACT[Date])))

 I use this to take the previous measure which simply converts finance hours into FTEs (Full-Time Equivalents). 

I then have the following measure to create a forecast based off of those actuals:

MEASURE - FTE - Finance FTE Forecast = (SUM(Finance_Actuals_FACT[Hours])/133.33)/(MONTH(NOW())-2) * 12

Essentially takes the actuals to date, divides by the number of months we have actuals, then multiplies by 12 to give me an annual forecast. 

The final measure to get the cumulative line I have as follows:

MEASURE - Cumulative FTE Forecast = 
CALCULATE([MEASURE - FTE - Finance FTE Forecast],
Filter(
    ALLSELECTED(Finance_Actuals_FACT),
'Finance_Actuals_FACT'[Date]<= MAX(Date_TABLE[Date])))

This provides me with the correct total based on current actual data but only spreads the data over the months we have recorded actuals, I need a forecast trend line which just averages the data over the 12 month period. 

Any help much appreciated!

[JJT1990]

Still looking for help with this one, I'm wondering if I can use the DIVIDE function at all to divide my current forecast value (annaul) by ALLSELECTED current dates to give me a full 12 month forecast

[v-rzhou-msft]

Hi @JJT1990 

Do you want to average the data in latest 12 months?

My Data table:

Sum = SUM(Sheet4[ValorStocks])

I build a calendar table.

Calendar = 
VAR _T = ADDCOLUMNS(CALENDAR(EOMONTH(MIN(Sheet4[Date]),-1)+1,EOMONTH(MAX(Sheet4[Date]),12)),"Year",YEAR([Date]),"Month",MONTH([Date]),"MonthName",FORMAT([Date],"MMM"),"YearMonth",YEAR([Date])*100+MONTH([Date]))
VAR _T2 = ADDCOLUMNS(_T,"RANK",RANKX(_T,[YearMonth],,ASC,Dense))
RETURN
_T2

Please try this measure: 

Measure = 
DIVIDE(SUMX(FILTER(ALL('Calendar'),'Calendar'[RANK]<=MAX('Calendar'[RANK])&&'Calendar'[RANK]>MAX('Calendar'[RANK])-12),[Sum]),12)

Result is as below.

If this reply still couldn't help you solve your problem, please share a sample with me by your Onedrive for Business and show me the result you want. this may make it easier for me to understand your requirement.

Best Regards,

Rico Zhou

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

[JJT1990]

thanks for your response. 

Essentially I have 4 months of actual data in this financial year (jan 2021- dec2021)

I have made a measure to sum this data, divide it by current number of months, and multiply by 12 to give me a rough forecast based on average actuals booked so far this year. 

In my graph above I would expect the green line to be a straight diagonal and go to the end of the year, not ramp up for 4 months then flatten

does that make sense?

[JJT1990]

bump - still need help with this