Forum Discussion

Gregs138's avatar
Gregs138
Helper I
9 years ago
Solved

DAX TOTAL YTD problem

Hi there, trying to solve what should seem like a fairly straightforward reporting/visual problem.  I am trying to compare LY Total Sales to CY Total Sales using a line chart by month   The problem...
Need Help
  • Gregs138's avatar
    Gregs138
    9 years ago

    Just some follow up on this for anyone that is having the same problem.

     

    The solution ended up being to add a filter to the YTD() function.  It is stated that it is an optional filter, however it seems to be rather necessary or else your YTDTotal will carry the last total(today) until the end of the year.  This is a little buggy imo but manageble with the custom filter parameter.

     

    Here was my fix:

     

    YTD Sales = TOTALYTD(SUM(SalesTable[SalesAmount]),'Order Date'[Date],filter('Order Date',[Date] < NOW()))

  • Eric_Zhang's avatar
    Eric_Zhang
    9 years ago
    Gregs138 wrote:

    Just some follow up on this for anyone that is having the same problem.

     

    The solution ended up being to add a filter to the YTD() function.  It is stated that it is an optional filter, however it seems to be rather necessary or else your YTDTotal will carry the last total(today) until the end of the year.  This is a little buggy imo but manageble with the custom filter parameter.

     

    Here was my fix:

     

    YTD Sales = TOTALYTD(SUM(SalesTable[SalesAmount]),'Order Date'[Date],filter('Order Date',[Date] < NOW()))

    Gregs138

    Thanks for your sharing. You can mark your reply as solution to close this thread. People who may have the same question can benefit from the solution.

Gregs138's avatar
Gregs138
Helper I
9 years ago

Just some follow up on this for anyone that is having the same problem.

 

The solution ended up being to add a filter to the YTD() function.  It is stated that it is an optional filter, however it seems to be rather necessary or else your YTDTotal will carry the last total(today) until the end of the year.  This is a little buggy imo but manageble with the custom filter parameter.

 

Here was my fix:

 

YTD Sales = TOTALYTD(SUM(SalesTable[SalesAmount]),'Order Date'[Date],filter('Order Date',[Date] < NOW()))

  • Eric_Zhang's avatar
    Eric_Zhang
    Microsoft Employee
    9 years ago
    Gregs138 wrote:

    Just some follow up on this for anyone that is having the same problem.

     

    The solution ended up being to add a filter to the YTD() function.  It is stated that it is an optional filter, however it seems to be rather necessary or else your YTDTotal will carry the last total(today) until the end of the year.  This is a little buggy imo but manageble with the custom filter parameter.

     

    Here was my fix:

     

    YTD Sales = TOTALYTD(SUM(SalesTable[SalesAmount]),'Order Date'[Date],filter('Order Date',[Date] < NOW()))

    Gregs138

    Thanks for your sharing. You can mark your reply as solution to close this thread. People who may have the same question can benefit from the solution.

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      I am don't understand why it is nesessary to use another dateset (Order Date[Date]) in the TotalYTD formula? Why can't you use the date column in your Sales Table? I have highlighted this below and would appreciate if you can share your knowlege:

       

      TOTALYTD(SUM(SalesTable[SalesAmount]),'Order Date'[Date]

       

      Tnx