SSAS, MDX, calculated member, pytd, reference on another value/month

Question

this task bugs me now since one week and I can't find a solution. I'm trying to extend a simple SSAS reporting solution my co-worker build. The goal is to easily access the ytd and pytd values to the sales numbers. Everything works perfectly there's just one little thing which doesn't make sense and would be, in my point of view, a nice-to-have feature.

My problem is, when I calculate the pytd field as a calculated member in MDX and use it in a report everything works except the value for the actual year on the top 'year' level. It shows the total of 2015 instead the pytd value which would be January - February 2015.

To make it more visible here is my query: pytd Query, works perfect, drilldown is no problem

and here is a sample picture how my result looks like: sample result

As you can see, the pytd data for 2016 is the total of 2015. But I would like to see the year to date value of February 2015 there. To make clear, I could see the information easily, by drilling down and looking at the data on the monthly based level. But we would like to see the correct information on the year level as well.

So I tried to adjust my mdx statement to show a different pytd value in the actual year row:

IIf(

[Zeit].[Year - Month - Date].CurrentMember is [Calendar 2016],

([Measures].[Sales Amount pytd],[Zeit].[Year - Month - Date].[Month].[Februar 2016]),

Aggregate( PeriodsToDate([Zeit].[Year - Month - Date].[Year], ParallelPeriod(
[Zeit].[Year - Month - Date].[Year], 1, [Zeit].[Year - Month - Date].CurrentMember ) )

,[Measures].[Umsatz] ))

So the query for itself works fine but now I tried to make it dynamic, so I need a parameter or a set to get the actual month + current year into the filter: Format(Now(),"MMMM yyyy") which results 'February 2016'

so from the syntax it should look/work like this: ([Measures].[Sales Amount pytd],[Zeit].[Year - Month - Date].[Month].[Format(Now(),"MMMM yyyy")])

But it doesn't work. The time dimension was added by my coworker via 'the add BI' wizard of SSAS. I don't like the way it's built up, that every Month has the year attached but I have to deal with it. I think changing the time dimension has a high risk of makeing the project unusable.

Has anybody an idea how to add the current year and month dynamically via a parameter or something else?

I'm looking forward to your ideas. :)

whytheq whytheq · Accepted Answer · 2016-02-16T08:39:17

You need to fully qualify your member in the logical section of you iif:

[Zeit].[Year - Month - Date].CurrentMember 
      IS [Calendar 2016]  //<<please use the full name, from your cube, for this member

This will not execute:

([Measures].[Sales Amount pytd],[Zeit].[Year - Month - Date].[Month].[Format(Now(),"MMMM yyyy")])

You will need to do something like this:

WITH
MEMBER [Measures].[Caption for today] AS
  Format(Now(), 'MMMM yyyy')
MEMBER [Measures].[String for today] AS
  '[Zeit].[Year - Month - Date].[Month].&[' + [Measures].[Caption for today] + ']'
SET [Measures].[today] AS
  StrToMember([Measures].[String for today] , CONSTRAINED)
...
...

SSAS, MDX, calculated member, pytd, reference on another value/month

1 Answers