Forecasting future months using previous months data on a fiscal period

[garcia_mars]

Hi,

I need some help on creating a measure to calculate a forecasted monthly value using the average of the prior months in the fiscal year. The result showing in my table isnt the correct average. I am wondering where it is coming from. I used the below formula:

Average Corp Card Spend = AVERAGEX(
    VALUES('Calendar Dates'[Month/Year]),
    CALCULATE(AVERAGE('Actual Spend'[Corporate Card Spend]))
)

And also, i want it to appear only on the months which have blank actual corp card values (in the example: June-Oct only)

I've also tried the following formula but didnt work to:

Average Corp Card Spend = AVERAGEX(
    VALUES('Calendar Dates'[Fiscal Year]),
    CALCULATE(AVERAGE('Actual Spend'[Corporate Card Spend]))
)

the result became:

Appreciate the help!

[v-chuncz-msft · Accepted solution]

@garcia_mars,

You may try CALCULATETABLE Function and ALLEXCEPT Function as shown below.

Measure =
IF (
    ISBLANK ( SUM ( 'Actual Spend'[Corporate Card Spend] ) ),
    AVERAGEX (
        CALCULATETABLE (
            VALUES ( 'Calendar Dates'[Month/Year] ),
            ALLEXCEPT ( 'Calendar Dates', 'Calendar Dates'[Fiscal Year] )
        ),
        CALCULATE ( SUM ( 'Actual Spend'[Corporate Card Spend] ) )
    )
)

[garcia_mars]

Works perfectly! thankyou @v-chuncz-msft

[garcia_mars]

@v-chuncz-msft

Need your further help...

Just realized it doesnt show any totals for that column.  How would i total them?

And also, I wanted to create a total (actual +forecasted) column - I did the below measure and it works on the row level, but the grand total included only the actual value.

Actual + Forecasted = [Total Corp Card Spend]+[Forecasted Monthly Corp Card Spend]