How to create MTD, MTD for the previous year

Question

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

Answer 1

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.