Hi,
I am looking for a bit of a hand-up on some MDX code. (Not the final solution - just some pointers)
We've changed financial year ends (from September to March). We are running a BPC Consolidation YTD Model (Classic).
One of the things we need is to compare is similar periods Income Statement with a YTD Measure (Periodic works fine). For example. April 2018 with April 2017.
However, April 2018 has one month of trading. April 2017 will have 7 months of trading. So we need to subtract March 2017 (that has 6 months of trading) in our the calculation.
Similarly, October 2018 will have 7 months of trading, but October 2017 only has 1 month of trading. So we need to add the prior 6 months (Sept 2017 - Mar 2017) to Oct 2017 to get the comparative right number.
I hope this makes sense. (It's a bit tricky to get your head around at first.)
So, I will need to create my Custom Measure / Measure Formula starting with the standard BPC YTD and go from there. First I need to ignore AST and LEQ accounts.
From there I need to work out which Period I am in:
So, where I am needing help is are there MDX functions that can help with this? To hand-code each month is very tedious and will result in a very long MDX statement.
Any ideas which MDX functions I can use to compare periods? Any other pointers and tips? Anyone done something similar? Some possible code snippets?
(I am on SAP BPC 10.1, NW 7.51 SP3 on HANA 1.12)
Kind Regards
Nick