Cash Flow Opening balance for MTD and YTD columns

[ZubairCSC]

Hi All,

I  want to get Last month ending balance in my MTD column of cash flow and last year ending balance in my YTD column of cash flow as you can see in above picture.

Can anybody share with me the business rule? I have tried following business rule but it is capturing only at the end of the year balance.

Dim currView As ViewMember = ViewMember.GetItem(api.Pov.View.MemberPk.MemberId) 'Current view member ID

Dim priorQtr As Integer = api.Time.GetLastPeriodInPriorQuarter(api.Pov.Time.MemberId) 'Prior QTR member ID

Dim priorQtrName As String = api.Time.GetNameFromId(priorQtr) 'Prior QTR name

#End Region

' Bring in Beginning Cash and Ending Cash to the Cashflow hierarchy in Flow

If currView.Name = "YTD" Then

api.data.calculate("F#CF_BegCash" & Target & " = RemoveZeros(F#EndBal:T#PovPriorYearM12" & Source &")","A#CashTot.Base")

Else If currView.Name = "Periodic" Then

api.data.calculate("F#CF_BegCash" & Target & " = RemoveZeros(F#EndBal:T#PovPrior1" & Source &")","A#CashTot.Base")

Else If currView.Name = "MTD" Then

api.data.calculate("F#CF_BegCash" & Target & " = RemoveZeros(F#EndBal:T#PovPrior1" & Source &")","A#CashTot.Base")

End If

api.data.calculate("F#CF_EndCash" & Target & " = RemoveZeros(F#EndBal" & Source &")","A#CashTot.Base")

Please help!

[EricOsmanski · Accepted solution]

You will want to make this Flow Member to be Dynamic Calc. Here is an example from the CPM Blueprint with can be downlaoded from the Solution Exchange OpenPlace.

Dim currView As ViewMember = ViewMember.GetItem(api.Pov.View.MemberPk.MemberId) 'Current view member ID

Dim priorQtr As Integer = api.Time.GetLastPeriodInPriorQuarter(api.Pov.Time.MemberId) 'Prior QTR member ID

Dim priorQtrName As String = api.Time.GetNameFromId(priorQtr) 'Prior QTR name

If currView.Name = "YTD" Then

Return api.Data.GetDataCell("A#Cash:F#EndBal:T#PovPriorYearM12")

Else If currView.Name = "Periodic" Then

Return api.Data.GetDataCell("A#Cash:F#EndBal:T#PovPrior1")

Else If currView.Name = "QTD" Then

Return api.Data.GetDataCell("A#Cash:F#EndBal:T#[" + priorQtrName + "]")

Else

Return Nothing

End If

[ZubairCSC]

Hi Eric,

Thank you so much for your reply. I am new in the OneStream. How can I reference this DynamicCalc member in Cube view? I am trying to build this dynamic member in U8 dimension and trying to reference that U8 member in my Flow dimension  in my Cube View. I am attaching the picture. I m attaching U8 Dimension and business rule picture also.