Your error occurs because you are using CLOSINGPERIOD()
function with a member expression (CurrentMember.PrevMember
). CLOSINGPERIOD()
is meant to be used with a level expression.
However, the issue you seem to be trying to solve is calculating the MTD (Month-to-Date) and LYMTD (Last Year Month-to-Date) in your MDX Query. For MTD and LYMTD calculations you can use MTD()
and PARALLELPERIOD()
functions. The MTD()
function returns a set of members from the beginning of the current member to the current member itself, on the specified level. The PARALLELPERIOD()
function returns a member from a prior period in the same relative position as the specified member.
```mdx
WITH
MEMBER [Measures].[MTD Sales] AS
SUM(MTD([Charge Create Date].[Display Date Key].CurrentMember), [Measures].[Set 01 Charge Amount])
MEMBER [Measures].[LYMTD Sales] AS
SUM(
MTD(PARALLELPERIOD([Charge Create Date].[Year Number], 1, [Charge Create Date].[Display Date Key].CurrentMember)),
[Measures].[Set 01 Charge Amount]
)
SELECT
{
[Measures].[Set 01 Charge Amount],
[Measures].[MTD Sales],
[Measures].[LYMTD Sales]
} ON 0,
NON EMPTY
[Charge Create Date].[Hierarchy].[Month Number Of Year].Members ON 1
FROM [Transactions_New]
In this query:
- The `[Measures].[MTD Sales]` measure is the sum of the `[Set 01 Charge Amount]` measure for the month-to-date period of the current member of the `[Charge Create Date].[Display Date Key]` hierarchy.
- The `[Measures].[LYMTD Sales]` measure is the sum of the `[Set 01 Charge Amount]` measure for the month-to-date period of the member that is at the same relative position as the current member of the `[Charge Create Date].[Display Date Key]` hierarchy, but one year earlier.