topic Re: How to do projections for averages based off previous months for future months? in DAX Commands and Tips

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

Anonymous — Mon, 03 Jan 2022 15:02:45 GMT

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

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

ValtteriN — Mon, 03 Jan 2022 16:11:59 GMT

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!

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

PaulOlding — Mon, 03 Jan 2022 16:31:14 GMT

Hi Anonymous

You could have a measure like this.

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

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

Anonymous — Mon, 03 Jan 2022 17:54:27 GMT

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

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.

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

Anonymous — Mon, 03 Jan 2022 19:11:32 GMT

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:

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

PaulOlding — Mon, 03 Jan 2022 20:10:26 GMT

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

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

Anonymous — Mon, 03 Jan 2022 21:20:12 GMT

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?

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

PaulOlding — Tue, 04 Jan 2022 07:54:07 GMT

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

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

Anonymous — Tue, 04 Jan 2022 15:11:16 GMT

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