PeriodsToDate Function Return Nothing in calculated Measures.

I am new one in MDX. Our PeriodsToDate function does not return any value. We have set type property of our Date Dimension as time.

Actually CURRENTMEMBER does not return a valid value. So our PeriodsToDate function fail.

Pl see query below that return nothing

With MEMBER [Measures].[YTD  Actual]

AS

Aggregate

(

                PeriodsToDate

                (

                [DimDate].[CalendarHierarchyDateLevel].[Calendar Year]

                ,[DimDate].[CalendarHierarchyDateLevel].CURRENTMEMBER

                )

,[Measures].[Actual Sales Amount]

)

select  {[Measures].[Actual Sales Amount],   [Measures].[YTD  Actual]} on 0 from [MyCube]

But When we give Year as Hardcoded then it return correct value.

with MEMBER [Measures].[YTD  Actual]

AS

Aggregate

(

                PeriodsToDate

                (

                [DimDate].[CalendarHierarchyDateLevel].[Calendar Year]

                ,[DimDate].[CalendarHierarchyDateLevel].[Calendar Year].&[2008]

                )

,[Measures].[Actual Sales Amount]

)

select  {[Measures].[Actual Sales Amount],   [Measures].[YTD  Actual]} on 0 from [MyCube]

Please help me to resolve this issue.

Thanks in advance.


August 28th, 2015 7:39pm

Hi Azhar,

In this scenario, since you use CURRENTMEMBER in your calculated measure, you should apply dimension members on rows so that it can get the date range to slice measure. The CURRENTMEMBER is always a dynamic member depends on the selecting members on rows. That's the reason why you can get correct result when you hard code a member. So your query should be like:

With MEMBER [Measures].[YTD  Actual]

AS

Aggregate

(

                PeriodsToDate

                (

                [DimDate].[CalendarHierarchyDateLevel].[Calendar Year]

                ,[DimDate].[CalendarHierarchyDateLevel].CURRENTMEMBER

                )

,[Measures].[Actual Sales Amount]

)

select  {[Measures].[Actual Sales Amount],   [Measures].[YTD  Actual]} on 0, 
[DimDate].[CalendarHierarchyDateLevel].[Calendar Year].members on 1

from [MyCube]

Reference:
CurrentMember (MDX)

Regards,

Free Windows Admin Tool Kit Click here and download it now
August 29th, 2015 3:36am

Thanks Simon,

this help me.

August 30th, 2015 4:06pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics