How to create MTD, MTD for the previous year

Lisa Kruger 60 Reputation points
2023-01-16T17:28:58.36+00:00

Hello Everyone,

Pretty new to SSAS MD. My apologies if I ask some redundant questions. I want to create MTD and MTD for the previous year. I have been doing some reading on hierarchies and dimensions. I created a dimension FundRelDate. This date exists in the FACT Table and I created a relation between the FACT and Calendar Dimension in the DSV, all the fields in this DIM are varchar. When I created a dimension FACTRELDATEDIM, I did not create any hierarchies in the middle pane. My questions are:

  1. Do I need to create a hierarchy in the middle pane?
  2. I have a calculation as FUNDMTDLY as below is this correct as it is showing NULL, I have data , is it because the attributes are wchar? Thanks
Sum(
  PeriodsToDate(
    [FUNDRELDate].[Month Of Year],
    ParallelPeriod(
        [FUNDRELDate].[Year], 
        1, 
        [FUNDRELDate].[Date Key].CurrentMember
       )
    ),
  [Measures].[FUNDRELAMNT
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,344 questions
SQL Server | Other
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Anonymous
    2023-01-17T07:58:09.4466667+00:00

    Hi @Lisa Kruger

    You can refer to the content in the following link, which may be helpful for your question.

    https://learn.microsoft.com/en-us/analysis-services/tabular-models/calculation-groups?view=asallproducts-allversions

    https://www.sqlshack.com/time-intelligence-in-analysis-services-ssas-tabular-models/

    https://aniruddhathengadi.blogspot.com/2011/03/how-to-calculate-ytd-qtd-mtd-and-wtd.html

    Best regards,

    Percy Tang


    If the answer is the right solution, please click "Accept Answer". 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 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.