Link to home
Start Free TrialLog in
Avatar of gosi75
gosi75Flag for Iceland

asked on

YTD calculation in MDX

Hi,

I am trying to make an MDX query that calculates the YTD for current month.

My hierarchy is [Year-Quarter-Month-Day] but this query is only returning the same value for [YTD Amount] as [Amount] is giving,
f.eks.
Year         Quarter       Month              Amount       YTD Amount
2003            1              January             1,860            1,860
2003            1               February           2,291            2,291
2003             1              Martz                2,567             2,567

Any idea why this query is not calculating YTD?
WITH MEMBER [Measures].[YTD Amount] AS
 
'SUM(
PeriodsToDate([DATE].[Year-Quarter-Month-Day].[Month],[DATE].[Year-Quarter-Month-Day].currentMember), [Measures].[Amount]
)'
 
SELECT
    {[Measures].[Amount],[Measures].[YTD Amount]} ON COLUMNS,
    [DATE].[Year-Quarter-Month-Day].[Month] ON ROWS
 
FROM [SKY]

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of PFrog
PFrog
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I should really also point out why your original MDX had problems...

PeriodsToDate needs to know which time attribute to aggregate up to. i.e do you want day to date, week to date, month to date or year to date.

In your YTD Amount member expression, you specified the first parameter as
     [DATE].[Year-Quarter-Month-Day].[Month]
This tells PeriodToDate function to return (and subsequently aggregate) all facts from the start of the month. (i.e. return a 'month to date' figure).

Consequently, if you change the fist parameter to
     [DATE].[Year-Quarter-Month-Day].[Year]
if would actually give you a YTD figure as you want. However, the built in YTD() function saves you the bother.

Hope this is clear enough - if not please shout...
Avatar of gosi75

ASKER

Thanks alot for the help, now I can go home from work extremely happy man :-)