locked
Forecasting formula causing PowerPivot to Crash & Burn RRS feed

  • Question

  • Running PowerPivot for Excel (standalone).

    I had a simple forecast formula calculated column(s) set up in my sales data. Worked great: 

    =CALCULATE(sumx('Sales','Sales'[Quantity]/(Month(Now())-1)*12),'Sales'[Year]=2010).  (ok it's not exactly poetry but it works).

    I had this for Sales, volume, quantity and one other measure.

    Now with January sales data to play with I changed the year to 2011.  It appeared to work.  Save Powerpivot.  Sometimes close Excel completely. Open it up and...

    ============================
    Error Message:
    ============================

    Exception from HRESULT: 0x800A03EC

    ============================
    Call Stack:
    ============================


    Server stack trace:
       at System.Windows.Forms.Control.MarshaledInvoke(Control caller, Delegate method, Object[] args, Boolean synchronous)
       at System.Windows.Forms.Control.Invoke(Delegate method, Object[] args)
       at Microsoft.AnalysisServices.Modeler.FieldList.GeminiProxy.MethodInvokeCallBack(String memberName, String dispID, BindingFlags flags, Object[] args, Boolean ignoreRetry)
       at Microsoft.AnalysisServices.Modeler.FieldList.GeminiProxy.HandleMethodInvoke(IMethodCallMessage callMessage)

    Exception rethrown at [0]:
       at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
       at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
       at Microsoft.Office.Interop.Excel.OLEDBConnection.Reconnect()
       at Microsoft.AnalysisServices.Modeler.FieldList.InProcServer.LoadOLEDBConnection(Boolean raiseCompleteEvent)

    ============================

    All data corrupted.  I reworked the formula, changing to a dimdate[year]=2011. deleted all formulas and recreated them from scratch.  Refreshed the data source.  Everything I did - would appear to work but as soon as I close the sheet and reopen it and... Boom goes the dynamite - and my data is corrupted.

    What confuses the heck out of me is that is worked fine last year - no problems. Is 2011 just an unlucky year?  Will have to create the measure on the fly rather than calculated column?

    I've wasted about 5 hours recreated my data from scratch - SEVERAL TIMES - now.  How can I get this to work - properly - ?

    Friday, February 18, 2011 8:39 PM

Answers

  • Sorry to hear youi're having problems, John. Wouild it be possible for you to open an issue on the connect site with the workbook so we can take a look?

    thanks

    Ashvini Sharma

    Analysis Services

     

    Wednesday, February 23, 2011 5:10 AM
  • Sorry I can't.  I've also given up (on that file).  It had several other 'bugs' so I started fresh.  I have not used a calculated column again, but instead decided to go calculated measure - just to play it safe.   So far it seems to be working.
    Thursday, March 3, 2011 7:50 PM

All replies

  • Sorry to hear youi're having problems, John. Wouild it be possible for you to open an issue on the connect site with the workbook so we can take a look?

    thanks

    Ashvini Sharma

    Analysis Services

     

    Wednesday, February 23, 2011 5:10 AM
  • Sorry I can't.  I've also given up (on that file).  It had several other 'bugs' so I started fresh.  I have not used a calculated column again, but instead decided to go calculated measure - just to play it safe.   So far it seems to be working.
    Thursday, March 3, 2011 7:50 PM