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

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics