cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Anonymous
Not applicable

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

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

1 ACCEPTED SOLUTION
Super User

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!

Proud to be a Super User!

8 REPLIES 8
Solution Sage

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
Not applicable

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:

Solution Sage

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
Not applicable

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?

Solution Sage

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

Anonymous
Not applicable

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

Super User

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!

Proud to be a Super User!

Anonymous
Not applicable

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.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Fabric Community Update - April 2024

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

#### Power BI Monthly Update - March 2024

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

Top Solution Authors
Top Kudoed Authors