Search code examples
ssasmdx

mdx combination aggregate, periodtodate and dimension limitation

I have Hours ytd measure with the following syntax:

    Aggregate(PeriodsToDate([Dim Time].[Fiscal Hierarchy].[Fiscal Year],
   [Dim Time].[Fiscal Hierarchy].CurrentMember),[Measures].[Hours])

And i have normal hours (1):

 ([Dim Product].[Product hours type 2].&[1],[Measures].[Hours])

Now i'm trying to combine both of these, to have normal hours counting from the start of the fiscal year.

Tried the following and many more, but all return NULL:

    Aggregate(PeriodsToDate([Dim Time].[Fiscal Hierarchy].[Fiscal Year],
   [Dim Time].[Fiscal Hierarchy].CurrentMember),
   ([Dim Product].[Product hours type 2].&[1],[Measures].[Hours]))
Solution

  • Good night of sleep solved the issue..

      (Aggregate((PeriodsToDate([Dim Time].[Fiscal Hierarchy].[Fiscal Year],[Dim Time].[Fiscal Hierarchy].CurrentMember),[Dim Product].[Product hours type 2].&[1]),[Measures].[Hours]))