YTD calculation till previous month in MDX
Hi all
was trying to find an answer on different forums without any success . I need to calculate the YTD value , but the trick is it should be till previous month and not till current month .
Example :
In July I have to show the ytd number for previous FinYear, in August July value PLUS June (which will be YTD for the prev FinYear) etc.
It does give me a YTD for Prev FinYear if I am checking numbers in July , but then it starts to calculate YTD from July , for current Fin Year only.
I am using this formula :
([Date].[Fiscal].
PrevMember,([Date].[Fiscal Date Calculations].[Year to Date],[Measures].[My DataValue]))
I also used Lag(1) - same result
([Date].[Fiscal].
CurrentMember.Lag
(1),([Date].[Fiscal Date Calculations].[Year to Date],[Measures].[My DataValue]))
Any suggestions ? would really appreciate your help
regards
December 4th, 2011 12:29am
Hi,
if you ask your query under this forum, you will get many suggestions:
http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/threads
Aftab Ansari
Free Windows Admin Tool Kit Click here and download it now
December 4th, 2011 4:02am
I actually managed to find an answer , so if anyone will find it useful - here are the formulas i used :
MEMBER
[Measures].[MyDataValue_YTD] AS
SUM
( {(OPENINGPERIOD([Date].[Fiscal].[Month]).LAG(11))
:
OPENINGPERIOD([Date].[Fiscal].[Month])},([Date].[Fiscal].PrevMember,[MEASURES].[My
DataValue]))
regards,
December 4th, 2011 6:18pm