Skip to Content
0
Former Member
Dec 08, 2014 at 03:39 PM

AVERAGE YTD Custom Measure

17 Views

All,

We have a requirement to create a custom measure to view Average YTD values for Balance sheet accounts. The formula for Average YTD is as below.

Jan Feb

Jan value *31/31 (Jan Value*31+Feb Value*28)/59 and so on.

For quarters and Year totals it should be last months value so for Q1 it would be same as March and for Yearly total it would be same as Dec.

We have stored no days in month and Year in stat accounts and used it in the below custom formula.

MEMBER [MEASURES].[DTD] AS 'SUM(PERIODSTODATE([%TIME%].[LEVEL00],[%TIME %].CURRENTMEMBER),([ACCDETAIL].[ACD_10000],[AUDITID].[AUD_FIN_01],[BANKTYPE].[BTY_10000],[FTPID].[FTP_10000],[FINACCOUNT].[980000001],[FINENTITY].[FINE_10000],[CATEGORY].[ACTUAL],[RPTCURRENCY].[LC],[MEASURES].[/CPMB/SDATA]))'; SOLVE_ORDER = 3 MEMBER [MEASURES].[PTDBAS] AS 'IIF(ISLEAF([%TIME%].CURRENTMEMBER),IIF(([%FINACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="AST" OR [%FINACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE") ="LEQ"),([%FINACCOUNT%].CURRENTMEMBER)*([ACCDETAIL].[ACD_10000],[AUDITID].[AUD_FIN_01],[BANKTYPE].[BTY_10000],[FTPID].[FTP_10000],[FINACCOUNT].[980000001],[FINENTITY].[FINE_10000],[CATEGORY].[ACTUAL],[RPTCURRENCY].[LC],[MEASURES].[/CPMB/SDATA]),0),0)'; SOLVE_ORDER = 3 MEMBER [MEASURES].[YTDAVG] AS 'IIF(ISLEAF([%TIME%].CURRENTMEMBER),SUM(PERIODSTODATE([%TIME%].[LEVEL00],[%TIME %].CURRENTMEMBER),([MEASURES].[PTDBAS]))/([MEASURES].[DTD]),0)'; SOLVE_ORDER = 4

Issues:

We get unexpected results some times in reports and sometimes we the required correct result.

We see 0 values in report if we just bring a single month but we see correct values in report if we bring more than one month in report.

We want this formula to work correctly for Quarters and Year Totals but currently it shows zero.

Please let me know in case you need any further information.

Regards

Nikhil