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

Reply
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:

JMcAnarney_0-1641221717406.png

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????

 

1 ACCEPTED SOLUTION
ValtteriN
Super User
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:
ValtteriN_0-1641226270951.png

 

End result:
ValtteriN_1-1641226288349.png

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

8 REPLIES 8
PaulOlding
Solution Sage
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:

JMcAnarney_0-1641237086904.png

 

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?

 

JMcAnarney_0-1641244805930.png

 

 

 

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.

ValtteriN
Super User
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:
ValtteriN_0-1641226270951.png

 

End result:
ValtteriN_1-1641226288349.png

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





Did I answer your question? Mark my post as a solution!

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
 
JMcAnarney_1-1641232424704.png

 

Any advice the first submission did not work.

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

April Fabric Community Update

Fabric Community Update - April 2024

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

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

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

Top Solution Authors