# Help needed with parameterized MDX for YTD calculations • ### Question

• Hi,

I need to calculate the YTD values for fiscal years using the parameterized MDX expression.My fiscal hierarchy looks like this [Fiscal month order] ->[fiscal Year].My requirement is that I have to use a paramater which accepts the month as a parameter and calculates the YTD value for the current year.I am available with a MDX expression which automatically finds the current month and travels back to the starting of the year and gives the YTd value for that year...

here is the MDX expression which picks the current month

PERIODSTODATE([Dim Invoice Date].[Fiscal Year - Month].[Fiscal Year], tail(nonemptycrossjoin(descendants([Dim Invoice Date].[Fiscal Year - Month].[Fiscal Month Order].members, 0, SELF), 1)).item(0) )

But my requirement is to take the month whatever the user passes in and need to travel back to the starting of the year.

I am currently working with this expression ,eventhough this is not the parameterized if i make it workable i want to replace this expression with a parameter.

with

member measures.PeriodsToDate1

AS

PeriodsToDate

([Dim Invoice Date].[Fiscal Year - Month].[Fiscal Year],strtomember("[Dim Invoice Date].[Fiscal Year - Month].[Fiscal Year].&.&[Q1-1-Sep]"))

Select

{measures.PeriodsToDate1 ,[Measures].[Usage Electric] }on columns From [DM ITI Utility]

But the error I am getting is that "The function expects a string or numeric expression for the argument.A tuple set expression was used"

Monday, August 23, 2010 9:08 PM

• u can pass the parameter on rows

some thing like this

{

PARALLELPERIOD([Date].[Fiscal].[Month],12,STRTOMEMBER('[Date].[Fiscal].[Month].&&')) : STRTOMEMBER('[Date].[Fiscal].[Month].&&') }

Regards

Gokul

• Marked as answer by Friday, September 3, 2010 4:26 AM
Monday, August 23, 2010 10:02 PM

### All replies

• Hi,

Try this query on formulated on adventure works

with

member

measures.PeriodsToDate1

AS

SUM( PERIODSTODATE([Date].[Fiscal].[Fiscal Year],[Date].[Fiscal].Currentmember),[Measures].[Sales Amount])

Select

{measures.PeriodsToDate1,[Measures].[Sales Amount]}

on columns,

{

PARALLELPERIOD([Date].[Fiscal].[Month],12,[Date].[Fiscal].[Month].&&) : [Date].[Fiscal].[Month].&& } on rows From [Adventure Works]

Regards

Gokul

Monday, August 23, 2010 9:59 PM
• u can pass the parameter on rows

some thing like this

{

PARALLELPERIOD([Date].[Fiscal].[Month],12,STRTOMEMBER('[Date].[Fiscal].[Month].&&')) : STRTOMEMBER('[Date].[Fiscal].[Month].&&') }

Regards

Gokul

• Marked as answer by Friday, September 3, 2010 4:26 AM
Monday, August 23, 2010 10:02 PM