6

Just need to get MAX date in ALL my Measures in the Cube. For instance, DateID is a Dimention, [Measure].[First Measure],...,...,[Second Measure].

How to get list of MAX(DateID) from all Measures in my Cube.

Mikhail Shcherbakov
  • 1,826
  • 16
  • 22
user2744070
  • 61
  • 1
  • 2
  • 2
    Do you mean the last `DateID` that has a value for any measure? And I would assume `DateID` is not a dimension, but an attribute (possibly the key attribute of the `Date` dimension). Is that correct? – FrankPl Sep 04 '13 at 12:19

1 Answers1

5

The following will get you the max date value associated with each measure...but you will have to manually create a calculated member corresponding to each measure.

WITH
    MEMBER [Measures].[Max Date - Internet Sales Amount] AS
        TAIL(
            NONEMPTY(
                [Date].[Date].[Date]
                ,[Measures].[Internet Sales Amount]
            )
            ,1
        ).Item(0).MemberValue
    MEMBER [Measures].[Max Date - Reseller Sales Amount] AS
        TAIL(
            NONEMPTY(
                [Date].[Date].[Date]
                ,[Measures].[Reseller Sales Amount]
            )
            ,1
        ).Item(0).MemberValue
SELECT
    {
        [Measures].[Max Date - Internet Sales Amount],
        [Measures].[Max Date - Reseller Sales Amount]
    } ON 0
FROM
    [Adventure Works]

If you want to get the single max date across all measures in the cube, you'll need to take a different approach.

Bill Anton
  • 2,920
  • 17
  • 23