Prior year in row with dynamic columns
Hello I am trying to create the below CV. I am struggling with the Account % Change from PY.
Is there a way to reference the column time - 1 year and still have it dynamic?
2022m1 ...
Forum Discussion
You could do a Row/Col reference in your Cube View. Or, you could create a dynamic UD8 member and drop that in your Cube View. As a best practice I'd recommend the UD8 route as those can be re-usable across numerous Cube Views.
The Dynamic Calc member formula then returns the variance. Included here is a check to make sure this does not run against Annotation type data cell intersections.
'This UD member is for reporting purposes.
'It displays the variance of the selected account's amount relative to the prior year.
'Only run if the view is not of an annotation type
If (Not ViewMember.IsAnnotationTypeViewId(api.Pov.View.MemberId)) Then
Return api.Data.GetDataCell("U8#None - T#POVPrior12:U8#None")
End If
Return Nothing
Your application specific POV may need to be updated, but this should get you 90% there.
Good luck,
Mike G
Archetype Consulting
Thanks Mike. The problem I am having is that POV prior 12 does not work with the .base in column 1. It is giving me the same PY T# for all three months in the quarter. For example if 2022 Q1 was selected It would give me 2021M1 for months 1-3.
