How to do projections for averages based off previous months for future months?

[Anonymous]

What I am trying to accomplish is the following:

Doing projections for months that have not occured by using the past averages of those months like the following:

In this picutre the projected december average is all the previous December average's and January is previous January etc.

Here are the following tables and calculations I created 

'Actuals :'

'Calendar'

What I also need as well is if there is a number in there for the actuals already than it is blank.  I have the following formula thus far:

Actual Projections (12 MA) =

IF( ISBLANK([Actuals :] ),

  Blank(),

CALCULATE( Averagex( DATESINPERIOD ('Test Calendar'[Date],

           LASTDATE('Test Calendar'[Date] ),

          -12,MONTH),

          [Actuals :] ) ))

But this is a moving average as Time currently stands 

Any advice????

[PaulOlding]

Hi @Anonymous 

You could have a measure like this.

Projections = 
IF(ISBLANK([Actuals :]),
    CALCULATE(
        AVERAGEX(
            VALUES('Calendar'[Year]),
            [Actuals :]
        ),
        REMOVEFILTERS('Calendar'),
        VALUES('Calendar'[Month])
    )
)

[Anonymous]

Actually I am idiot this is working, but one quick question.  Where do I need to put the sum feature so it sums total like:

[PaulOlding]

Do you mean having a projection value on the grant total line?

If so, the first problem is the if condition used so far: ISBLANK([Actuals :]).  this stops the measure returning a value for the Total row.

Next, for a SUM to work we'll need to iterate a table of years and months for which we want a projection.

I've split this into 2 measures, but you could combine them if you wish.

First the measure used before, but removing the IF condition

Monthly Projection = 
CALCULATE(
    AVERAGEX(
        VALUES('Calendar'[Year]),
        [Actuals :]
    ),
    REMOVEFILTERS('Calendar'),
    VALUES('Calendar'[Month])
)

Then the measure that assumes we want a projection for months later than the last actuals amount.

Projections = 
VAR _MaxActualDate = CALCULATE(EOMONTH(MAX(Actuals[Date]), 0), REMOVEFILTERS())
VAR _CurrentDate = MAX('Calendar'[Date])
VAR _Result = 
    CALCULATE(
        SUMX(
            SUMMARIZE('Calendar','Calendar'[Year],'Calendar'[Month]),
            [Monthly Projection]
        ),
        KEEPFILTERS(DATESBETWEEN('Calendar'[Date], _MaxActualDate +1, _CurrentDate))
    )
RETURN
    _Result

[Anonymous]

Question on second part of your formula:  On the 

VAR _MaxActualDate = CALCULATE(EOMONTH(MAX(Actuals[Date]), 0), REMOVEFILTERS())

I have a calculated field of actuals and a calendar table to where they are on seperate tables to where I do not have a actulas[date] column identifier.  What actuallly needs to go here?

[PaulOlding]

Actuals[Date] is the column in your amounts table used in the relationship to the calendar table

[Anonymous]

Thanks I believe I figured it out had to switch some references around.  Thanks I will mark as solution once i am 100% sure.

[ValtteriN · Accepted solution]

Hi,

Something like this should work:

Forecast =

IF(SELECTEDVALUE('Average'[Value])<>BLANK(),BLANK(),

CALCULATE(AVERAGE('Average'[Value]),all('Average'),'Average'[Value]<>blank(),MONTH('Average'[Month])=month(SELECTEDVALUE('Average'[Month]))))


Start data:
End result:

I hope this helps and if it does consider accepting this as a solution and giving the post a thumbs up!

[Anonymous]

Here is how I have it written I am having trouble getting it to translate:

Forecast =

 

Forecast =

IF(SELECTEDVALUE('GL298I'[Actuals])<>BLANK(),BLANK(),

     CALCULATE(AVERAGE('GL298I'[Actuals]),all('gl298I'),'GL298I'[Actuals]        <>blank(),MONTH('GL298I'[POSTING-DATE-2])=month(SELECTEDVALUE('GL298I'[POSTING-DATE-2]))))

The difference is a specific date under the data set with the  table that displays like: *Wednesday, March 14, 2001 (Long Date).

I am still getting zero under as forcasting: mm

Any advice the first submission did not work.