Post reply

Closing bal prev month / opening bal next month

  • December 9, 2003 at 12:46 am

    #106711

    Hi,

    I have a financial forecasting cube, where, for a single member the closing balance from the previous month must be the opening balance for the following month. The member is a calculated cell.

    I'm very new to MDX and not sure which function / syntax to use. Any help will be much appreciated.

    Thanks,

    Andre

  • December 9, 2003 at 2:23 am

    #485263

    Build the cube with a month dimension that you can total the change on that month.

    Then either handle it in the client app or:

    with

    member measures.YearTotalToDate as ' sum( { PeriodsToDate(Time.year, Time.month.currentmember) } , measures.currentmember ) '

    select { [measures].measureslevel.allmembers } on columns,

    { [Time].[month].members } on rows

    from yourcube

    Keith Henry

    DBA/Developer/BI Manager




    Keith Henry


    According to everyone I know I "do something with computers?" for a living, so there you go.

  • December 9, 2003 at 5:36 am

    #485293

    Thanks Keith,

    I've tried this syntax in AS on the calculated cell:

    with

    member measures.Amt as ' sum( { PeriodsToDate(Time.Year, Time.Month.currentmember) } , measures.currentmember ) '

    select { [measures].measureslevel.allmembers } on columns,

    { [Time].[Month].members } on rows from Test

    but get a syntax error:

    'expecting SELECT, near: 'sum({PeriodsToDate(Time.Year, Time.Month.currentmember)}..........

    The member is called 'OPENING BALANCE' that requires the calculated cell and is located in the REPSECT dimension:

    REPSECT

    ..=> CASHFLOW

    ....=> NETINCOME

    ......=> OPENING BALANCE

    The time dimension is YEAR=>MONTH

    For example, the 'OPENING BALANCE' should be calculated from the previous month's 'CASHFLOW' total:

    ................................2003

    ................................November..........December

    REPSECT

    ....CASHFLOW

    ........Net Income

    ...........Opening Balance........R 100,000.00...R 110,000.00

    ...........Current Debtors.........R 20,000.00....R 30,000.00

    ........Net Income Total..........R 120,000.00...R 140,000.00

    ........Current Creditors.........-R 10,000.00...-R 40,000.00

    ....CASHFLOW Total................R 110,000.00...R 100,000.00

    Again, thanks for your help

    Andre

  • December 12, 2003 at 5:38 am

    #485918

    Hi,

    I found that using the following in the calculated cell works very well:

    ([Report Section].[Module].&[CASHFLOW], [Measures].[Amt],parallelperiod(month))

    Parallelperiod finds the current member of the time dimension, and in this case (month) looks at the previous month dimension / measure.

    Regards,

    Andre

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply