Share via

MDX MTD For previous year

Lisa Kruger 60 Reputation points
2023-01-26T14:22:43.0133333+00:00

Hi All,

I posted the same question before, but with no luck. I am using SSAS MD model and I want to create a MDX Calculation to MTD and MTD for previous year. I have used the calculations from the following link

[https://social.msdn.microsoft.com/Forums/en-US/128e8f36-b8e6-4491-afc9-4b59ecbb87d3/mdx-filter-single-last-month-ytd-mtd-lytdlmtd-value?forum=sqlanalysisservices

Also I tried other links, I used the date dimension from our datawarehouse and TIME dimension from SSAS, but I am still getting NULL and ERRORS, I know we do not have data for 2023 in our dev, but it needs to give last year's data. Please see my query below. Please need help. The error message I am getting is "Query(6,13) THE CLOSINGPERIOD Function expects a level expression for the 1 argument. A member expression was used"


with
member [Measures].[YTD Internet Sales] as
    SUM(YTD ([Charge Create Date].[Display Date Key].CurrentMember.PrevMember), [Measures].[Set 01 Charge Amount])
member [Measures].[LYTD Internet Sales] as
    SUM(YTD(PARALLELPERIOD([Charge Create Date].[Year Number],1,
            CLOSINGPERIOD([Charge Create Date].[Display Date].CurrentMember.PrevMember)
            )
           ),
        [Measures].[Set 01 Charge Amount])
select
{ [Measures].[Set 01 Charge Amount],
  [Measures].[YTD Internet Sales],
  [Measures].[LYTD Internet Sales]
} on 0,
non empty
[Charge Create Date].[Hierarchy].[Month Number Of Year].Members on 1
from [Transactions_New]
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,361 questions
SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,263 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Amira Bedhiafi 20,176 Reputation points
    2023-08-03T16:47:11.1433333+00:00

    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.
    0 comments