I am trying to create a cash report that has the current day balance (which is the month to date amount) in one column and the prior month ending balance in another column. How do I create the prior month ending balance? I will then need to add a third column that shows the change in the balance. Any tips on how to add the prior month ending balance? Do I need to create a Time Perspective and if so how do I do that?
Thank you for any guidance you can provide - it all seems so easy watching the videos and then I try to create something myself and I can’t figure it out.
Hi Nicole,
Yes, you can create a Time Perspective to report the prior month value by using the MDX formula:
(ParallelPeriod([Time].Default.[Month], 1, [Time].Default.Currentmember), [Version].Default.&[ACT])
That said, this would mean that your cash flow calculation (current month value - prior month value) would have to be a template calculation, for every month you are running cash flow reporting for.
There is a more automated process (using LAG formulas) for most (if not all lines) of the 3 main cash flow categories. I would suggest reaching out to your Product Adoption consultant to talk about how you could implement this. Hope this helps.
Thanks,
Navin
1 Like