Forum Discussion

garcia_mars's avatar
garcia_mars
Frequent Visitor
8 years ago
Solved

Forecasting future months using previous months data on a fiscal period

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 averag...
  • v-chuncz-msft's avatar
    v-chuncz-msft
    8 years ago

    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] ) )
    )
)

     

v-chuncz-msft's avatar
v-chuncz-msft
Icon for Community Support rankCommunity Support
8 years ago

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's avatar
      garcia_mars
      Frequent Visitor
      8 years ago

      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]