CODEWITHSUNDEEP
mdxolap-cube
Nazmul Bhuiyan
Nazmul Bhuiyan

Reputation: 11

MDX Calculated Members with Named Set

I've a calculated member named CWTD (Current Week To Date) Sales and to get this working I need to pass a named set Yesterday.

Is it possible to include the named set into the Calculated Member so that I don't have to pass the named set in my select statement?

The reason I'm asking this as Power View doesn't show Named Set in field list.

Example:

WITH MEMBER [Measures].[CWTD Sales Test]
 AS Sum(
            PeriodsToDate(
                [Date].[NZLC Date].[NZLC Week],
                [Date].[NZLC Date].CurrentMember
            ),  
            [Measures].[Net Sales - Gross for IK]
        )
select 
  [Measures].[CWTD Sales Test] on columns,
  NON EMPTY [Product].[Product].[Product Group] on rows
from [Daily Sales]
where 
  Tail(NonEmpty([Date].[NZLC Date].[Day].MEMBERS,[Measures].[Gross Sales]),1)

Is there anyway I can include the where part into my calculated member?

Upvotes: 1

Views: 1250

Answers (2)

Dan
Dan

Reputation: 10680

Since your WHERE criteria represents the last day of your Gross Sales that has any data, you should simply use that tuple instead of .CurrentMember:

WITH MEMBER [Measures].[CWTD Sales Test]
 AS Sum(
            PeriodsToDate(
                [Date].[NZLC Date].[NZLC Week],
                Tail(NonEmpty([Date].[NZLC Date].[Day].MEMBERS,[Measures].[Gross Sales])).ITEM(0)
            ),  
            [Measures].[Net Sales - Gross for IK]
        )
select 
  [Measures].[CWTD Sales Test] on columns,
  NON EMPTY [Product].[Product].[Product Group] on rows
from [Daily Sales]

Upvotes: 2

whytheq
whytheq

Reputation: 35557

@Dan has answered your question but just as an alternative:

WITH 
SET [Yesterday] AS
  Tail(
    NonEmpty(
      [Date].[NZLC Date].[Day].MEMBERS
     ,[Measures].[Gross Sales]
    )
  )
MEMBER [Measures].[CWTD Sales Test] AS
  SUM(
    WTD([Yesterday].ITEM(0).ITEM(0))  
     ,[Measures].[Net Sales - Gross for IK]
  )
SELECT 
  [Measures].[CWTD Sales Test] ON 0,
  NON EMPTY [Product].[Product].[Product Group] ON 1
FROM [Daily Sales];

Upvotes: 2

Related Questions