My MTD Calculation is showing only NULL

BK 6 Reputation points
2023-01-13T14:56:50.9566667+00:00

Hi I have a calculation for Month to date as , in the browser it is only showing as NULL, please help.

Thanks

MTD=

SUM   
   (  
      MTD([Charge Create Date.Display Date].[Date])  
     , [Measures].[Set 01 Charge Amount]  
     )
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,247 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,231 questions
{count} votes

3 answers

Sort by: Most helpful
  1. PercyTang-MSFT 12,426 Reputation points Microsoft Vendor
    2023-01-16T09:39:39.13+00:00

    Hi @BK

    Maybe you can refer to the content in these links.

    [https://stackoverflow.com/questions/30127534/mtd-function-not-working-with-scope

    [https://social.msdn.microsoft.com/Forums/sqlserver/en-US/caa49401-9ad9-4abc-b3cb-d6c6ed66331c/ssas-calculated-measure-ytd-using-named-set?forum=sqlanalysisservices

    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".

    1 person found this answer helpful.
    0 comments No comments

  2. Hupkes, W.P. (Wouter) 21 Reputation points
    2023-01-14T19:46:10.85+00:00

    Please check:

    • Is the "Charge Create Date.Display Date" field a valid date field. If it is not, the MTD function will not be able to use it to calculate the month-to-date value.
    • Is the "Set 01 Charge Amount" field a valid numerical field. If it is not, the SUM function will not be able to use it to calculate the total.
    • Is the date field in the correct format, as the MTD function may be expecting a specific format.
    • Is there any missing data, as this can cause the MTD function to return null.
    • Is the "Charge Create Date.Display Date" field in the same hierarchy as the "Measures"."Set 01 Charge Amount" field, as the MTD function expects both fields to be in the same hierarchy.
    0 comments No comments

  3. BK 6 Reputation points
    2023-01-16T14:47:43.38+00:00

    Thanks I had the same issue and this solution works for me.

    Thanks

    0 comments No comments