YTD Calculation with MDX function

[Shamar]

Hello Gurus,

I have a crosstab which gives Monthwise sale. Next to month I need to show YTD Value.

For ex : If I select Jan,Feb   : Crosstab output should be Jan, Feb, YTD (total of jan & feb)
Suppose if I select Jan,May : Crosstab Output should be Jan,May,YTD (Total of Jan,Feb,Mar,Apr,May).

I have tried like this :

PeriodsToDate([Namespace].[Dim].[Hierarchy].[Year],[Namespace].[Dim].[Hierarchy].[Month]->?P_Month?). In this case, I cant able to do multiselect.

Kindly provide me MDX function for resolving this issue.

Thanks in advance,
Shamar
   

[MFGF]

Hello Gurus,

I have a crosstab which gives Monthwise sale. Next to month I need to show YTD Value.

For ex : If I select Jan,Feb   : Crosstab output should be Jan, Feb, YTD (total of jan & feb)
Suppose if I select Jan,May : Crosstab Output should be Jan,May,YTD (Total of Jan,Feb,Mar,Apr,May).

I have tried like this :

PeriodsToDate([Namespace].[Dim].[Hierarchy].[Year],[Namespace].[Dim].[Hierarchy].[Month]->?P_Month?). In this case, I cant able to do multiselect.

Kindly provide me MDX function for resolving this issue.

Thanks in advance,
Shamar

Hi,

For a YTD calculation you only need to prompt for a single month member - the closing month. The periodsToDate() function will return all month members belonging to the parent year, up to and including the month you specify. So in this case if you chose May in the prompt, the periodsToDate() function would return Jan, Feb, Mar, Apr and May.

By the looks of your description, you require the opening month in the set (ie Jan), the prompted-for month (ie May), and the aggregate of the measure for all months in the set (ie the total for January through to May) - is that correct?

If so, you're going to need three query calculations:

Opening Month:
openingPeriod([your Month level],ancestor([your Month level] -> ?P_Month?,[your Year level]))

Prompted Month:
[your Month level] -> ?P_Month?

YTD:
aggregate(currentMeasure within set periodsToDate ([your Year level],[your Month level] -> ?P_Month?))

Cheers!

MF.

[Shamar]

Hi MFGF,

Thanks you so much!!!

I have other issue, kindly suggest some idea on this,

1. Is it possible to get multiple selection in Month Prompt, since [month Level]-> ?P_Month? allows only single select.


Thanks,
Shamar

[MFGF]

Hi MFGF,

Thanks you so much!!!

I have other issue, kindly suggest some idea on this,

1. Is it possible to get multiple selection in Month Prompt, since [month Level]-> ?P_Month? allows only single select.


Thanks,
Shamar

Hi,

That doesn't seem to make any logical sense? How can you get a YTD result if you choose two different months? Can you explain your requirement?

MF.