06-08-2022 05:29 PM - last edited on 05-03-2023 09:02 AM by JackLacava
Hi all,
I have built out my cube view that shows headcount change for 2019 M12, I want to be able to capture the ‘Ending Balance Headcount’ for 2020 M1 as its ‘Begin Balance’.
Will a rule be the way to go in order to achieve this? Unsure on where to go from here.
Thank you.
Solved! Go to Solution.
06-09-2022 05:28 AM - edited 06-09-2022 05:43 AM
Hi DGC
If you only like to show the beginning balance in a cubeview, you don't need a rule, you can specify the pov F#EndingBal:T#PovPriorYearM12 directly in a column of the cubeview.
If you like to store the information in a dedicated flow member you can do use a member formula as well.
Here it is the simplest possible solution:
api.Data.Calculate("F#BegBal_Calc=F#EndingBal:T#PovPriorYearM12")
If you like, you can create a drill down path as well
'Simple formula drill down
Dim result As New DrillDownFormulaResult()
'Define Explanation to be shown in drill title bar
result.Explanation = "Roll Forward: F#BegBal_Calc = F#EndingBal:T#PovPriorYearM12"
'Add a row below per each drill result to be shown
result.SourceDataCells.Add("F#EndingBal:T#PovPriorYearM12")
Return result
There are more advanced time functions to use than T#PovPriorYearM12 but it is the easiest to understand.
I hope this helps and kind regards
You can also use the member formula builder from the market place solution administrator solution tools:
06-09-2022 05:28 AM - edited 06-09-2022 05:43 AM
Hi DGC
If you only like to show the beginning balance in a cubeview, you don't need a rule, you can specify the pov F#EndingBal:T#PovPriorYearM12 directly in a column of the cubeview.
If you like to store the information in a dedicated flow member you can do use a member formula as well.
Here it is the simplest possible solution:
api.Data.Calculate("F#BegBal_Calc=F#EndingBal:T#PovPriorYearM12")
If you like, you can create a drill down path as well
'Simple formula drill down
Dim result As New DrillDownFormulaResult()
'Define Explanation to be shown in drill title bar
result.Explanation = "Roll Forward: F#BegBal_Calc = F#EndingBal:T#PovPriorYearM12"
'Add a row below per each drill result to be shown
result.SourceDataCells.Add("F#EndingBal:T#PovPriorYearM12")
Return result
There are more advanced time functions to use than T#PovPriorYearM12 but it is the easiest to understand.
I hope this helps and kind regards
You can also use the member formula builder from the market place solution administrator solution tools: