First - NEVER use member ID with space inside! Not "TEST ACCOUNT" but "TEST_ACCOUNT" or something like!
Correct Formula:
IIF([TIME].CURRENTMEMBER.PROPERTIES("LEVEL")="YEAR",(SUM([TIME].CURRENTMEMBER.CHILDREN,[A230000])+([TIME].CURRENTMEMBER.LAG(1),[A230000]))/5,NULL)
Last quarter of the previous year will be equal to prev year TOTAL: [TIME].CURRENTMEMBER.LAG(1)
Test report:
"I am trying to create a Member formula to check the average of Q1,Q2,Q3,Q4 of the current year and also prior year Q4 when reported on TOTAL year" - sorry, but absolutely not clear what do you want to achieve!
Please spend some time to provide a clear explanation with a sample screenshot.
Hi Vadim,
Thanks for the quick response and apologies for not giving enough details.
In the member formula when the currentmember is a TOTAL node, I want to calculate average of 5 quarters going back which is 4 quarters of 2017 and last quarter Q4 of 2016.
I appreciate any help.
-Sailu
Add comment