How to I get YTD to work over multiple hierarchies

0
votes

I have a Dimension [Time] on my cube. I have the following properties on my time dimension.

  1. Date &[2015/12/25]
  2. Month &[2015/12]
  3. Year &[2015]
  4. Week &[2015wk52]

Arranged into 2 hierarchies.

[Time].[Y - M - D]

  • Year
  • Month
  • Date

and

[Time].[Y - WK]

  • Year
  • Week
  • Date

I also created a measure

[Measures].[Foo YTD] AS
AGGREGATE(YTD(), [Measures].[Foo])

This seems to work great when I use the Y M D dimension in excel. But when I use the Y WK hierarchy, all my weeks return the same data.

What am I doing wrong?

My attribute relations should be correctly setup I think.

1
ssasmdxcubeolap-cube
did you check the settings I suggested in my answer? – whytheq
@whytheq Period to date works fine... It seems YTD works on a default hierarchy...>_< – Aron
ok - but is the attribute you are feeding into the YTD function set to type year in SSAS ? If it is not typed correctly in SSAS then this function will not work. – whytheq
hi Aron - did you find an answer to your question? – whytheq

1 Answers

0
votes

This maybe your problem.

In the definition of the YTD function: https://msdn.microsoft.com/en-us/library/ms146039.aspx

It has this remark:

the Ytd function is a shortcut function for the PeriodsToDate function where the Type property of the attribute hierarchy on which the level is based is set to Years.

You could prove that this is your problem by trying the PeriodsToDate option - if it then works you know that it is the attribute type that is to blame.