Projection of Future Months

[Anonymous]

I am trying to do yearly payroll projections on months that HAVE NOT occurred yet.  For example in the graph below I have the following formula for Monthly Projection =

Monthly Projection =

CALCULATE(

    AVERAGEX(

        VALUES('Calendar'[Year]),

        [Actuals:]

    ),

    REMOVEFILTERS('Calendar'),

    VALUES('Calendar'[Month])

)

What I want is something similar to this in excel =

How can I write a formula where this occurs, and I want the methodology be a rolling average based on the year.  For example, through may 31st 10 pay periods would occur.  I want the future months to be the average pay period *2.

[tamerj1]

Hi @Anonymous 

can you please advise how did you calculate the yellow highlighted values?

[Anonymous]

Those are random numbers ideally it would be an average of the prior months i.e. average of all Aprils

[tamerj1]

Can you share some sample data?