MDX question: Create a calculation to obtain the SUM of a measure in the previous month

KamiFen 121 Reputation points
2022-08-26T05:52:57.117+00:00

Hi,
I need to create a calculation, with the SUM of a measure ([Measures].[SalesAmount]), like:

SUM([Measures].[SalesAmount])-SUM([Measures].[Sales Amount] in the previous month)

Can anyone help me?

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,256 questions
0 comments No comments
{count} votes

Accepted answer
  1. Joyzhao-MSFT 15,571 Reputation points
    2022-08-26T06:29:18.383+00:00

    Hi @KamiFen ,
    You could use the Lag function, and the relevant position of your time dimension.
    If you are at Time.Hierarchy.Day then you would use:

    Time.Hierarchy.CurrentMember.Parent.Lag(1)  
    

    If you are already at the Month level of the heirarchy you could just use the Lag function:

    Time.Hierarchy.CurrentMember.Lag(1)  
    

    Best Regards,
    Joy


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


0 additional answers

Sort by: Most helpful