Dynamic Time Filters with Calculated Measures

Good afternoon,

I'm not usually one for posting for help in forums, but I am absolutely stuck on this:

I have an accounting information cube, and a properly calculated PNL measure that contains the change from day to day at the [Calendar Date] level. If I aggregate it (standard sum aggregation) by including multiple dates, I get the correct PNL over that period. Pretty standard.

We are using Excel 2007 pivot tables as the front-end.

My problem is that my business users also want to be able to have a "PNL - Running Total" calculated measure that begins with a Zero value at whatever day they have selected first in their result set. We will then use that value in combination with other calculated measures to determine peak PNL amounts, days decline, standard deviation, etc. -- over whatever time period they choose.

I've experimented with [Time].CurrentMember.PrevMember logic, but it always gives me everything in the cube. I think that doing some sort of StrToMember() logic might be the way, but I can't get it to work.

I've had zero luck tracking down this issue on the web. If anybody can help me or point me in a helpful direction, I will be incredibly grateful.

Thanks,
Anthony J. Algmin

It seems so simple now. I think this is giving me what I need:

Sum(
(
([Time].[By Year - Month - Date].CurrentMember.FirstSibling:
[Time].[By Year - Month - Date].CurrentMember)

),
([Measures].[PNL])
)

That seems to be giving me a MTD Aggregate number -- not what I really need. Back to the drawing board.