CODEWITHSUNDEEP
sql-servermdx
bjornasm
bjornasm

Reputation: 2318

Get current month in where statement MDX

Lets say I want the current months sales from each store. I would do it with first make a calculated member:

SELECT
{[Measures].[Total sales]}
ON 0,
{[Organization].[Storename]} ON 1
FROM SALES
WHERE
[Period].[Period].[Month].&[2015]&[11]

To automate this to always be the last month, I would do something like this:

WITH  MEMBER [Measures].[Full Date] as 'NOW()'
MEMBER [Measures].[What Month] as 'MONTH([Full Date])'
Member [Measures].[What Year] as 'YEAR([Full Date])'

SELECT
{[Measures].[Total sales]}
ON 0,
{[Organization].[Storename]} ON 1
FROM SALES
WHERE
[Period].[Period].[Month].&[What Year]&[What month]

However, I get no data doing this. What do I do wrong? How should I do this?

Upvotes: 1

Views: 2991

Answers (1)

SouravA
SouravA

Reputation: 5243

It's looking for a member by the name&[What Year]&[What month] as if there is a literal member by this name in the "month" level.

Instead use StrToMember function inside your WHERE clause.

StrToMember("[Period].[Period].[Month].&[" + FORMAT([Measures].[What Year], "") + "]&["+ FORMAT([Measures].[What Month], "") + "]")

Upvotes: 1

Related Questions