CODEWITHSUNDEEP
mdxcubessas-2008
user2744070
user2744070

Reputation: 61

MDX SSAS - Max Date in Measure

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.

Upvotes: 6

Views: 4771

Answers (1)

Bill Anton
Bill Anton

Reputation: 2970

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.

Upvotes: 5

Related Questions