Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

TotalYTD in a Matrix

Hi y'all,

 

I am having difficulty getting totalYTD to work in my matrix. I currently have sales data between 2018 to May 3rd 2021. My goal is for totalYTD to forecast for the rest of the year and then I can visually shift through the dates using a date hierarchy.

 

The DAX I am currently using is as follows:

 

Year to Date = TOTALYTD(SUM('Sales Data'[Net Sales]),'Sales Data'[Day].[Date])

 

I issue might be because all my data is in one query rather than seperate queries?

 

Any help is greatly appreciated as well as any advice for doing this a different way!

 

Thanks!

 

Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi  Anonymous,

     

    The hierarchy should be in your original table rather than the calendar table,check my sample below:(remember to create a relationship between the 2 tables)

     

    Check my .pbix file attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

3 Replies

  • aj1973's avatar
    aj1973
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hi Anonymous 

    I think your issue is more like using Time intelligence function without a Calendar table. if you have gaps in your 'Sales Data'[Day].[Date]  then don't count on TOTALYTD to outcome an accurate result.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thank you for the reply!

       

      I created the calendar table, but I am still having the same issue as before where it just repeats the last sales data from May 3rd 2021. I want to be able to drill down through a date hierarchy. In the dax code what would that look like as I am unsure of how to link it to a hierarchy within the calendar table. I have the calendar hierarchy broken down between 'CalendarYear' 'CalendarQuarter' 'CalendarMonth' 'CalendarDate'.

       

      The DAX I am currently using is as follows:

       

      Year to Date = TOTALYTD(SUM('Sales Data'[Net Sales]),'Calendar Table'[CalendarDate])

       

      Thanks again!

       

      • v-kelly-msft's avatar
        v-kelly-msft
        Icon for Community Support rankCommunity Support
        5 years ago

        Hi  Anonymous,

         

        The hierarchy should be in your original table rather than the calendar table,check my sample below:(remember to create a relationship between the 2 tables)

         

        Check my .pbix file attached.

         

        Best Regards,
        Kelly

        Did I answer your question? Mark my post as a solution!