Forum Discussion

kingchad5's avatar
kingchad5
Icon for Helper I rankHelper I
9 years ago
Solved

TOTALYTD Returning BLANK

I am having some trouble with a simple TOTALYTD formula.  I am trying to count the total projects YTD.  My table has a relation from the Submitted date of the project and a Date table.  I have tried ...
Need Help
  • OwenAuger's avatar
    OwenAuger
    9 years ago

    kingchad5

     

    With the TOTALYTD or DATESYTD functions, year-to-date is defined relative to the maximum value of Dates[Date] in the filter context.

     

    If the maximum value of Dates[Date] is in 2025 and you haven't applied any Date filters,  and your 'Professional Services Quotes' table doesn't extend to 2025, then TOTALYTD changes the filter on Dates[Date] to the range 1/1/2025-31/12/2025 (assuming you have the complete 2025 calendar year in Dates), which would give you a BLANK result.

     

    Normally these functions are used in the context of days or months within a single year, and will return a YTD value within that year.

Sean's avatar
Sean
Icon for Community Champion rankCommunity Champion
9 years ago

Since you have a Date/Calendar table - DO NOT use the .[Date] notation - reference only Dates[Date] :smileyhappy:

kingchad5's avatar
kingchad5
Icon for Helper I rankHelper I
9 years ago

I get the same result, BLANK.  Could it have anything to do with the formatting of the columns?

 

Proposals2 YTD = TOTALYTD(COUNT('Professional Services Quotes'[Project Name]),'Dates'[Date]) 

  • kingchad5's avatar
    kingchad5
    Icon for Helper I rankHelper I
    9 years ago

     

    Also, when I add a Year Slicer and select 2017 I get results.

     

     

    • OwenAuger's avatar
      OwenAuger
      Icon for Super User rankSuper User
      9 years ago

      kingchad5

       

      With the TOTALYTD or DATESYTD functions, year-to-date is defined relative to the maximum value of Dates[Date] in the filter context.

       

      If the maximum value of Dates[Date] is in 2025 and you haven't applied any Date filters,  and your 'Professional Services Quotes' table doesn't extend to 2025, then TOTALYTD changes the filter on Dates[Date] to the range 1/1/2025-31/12/2025 (assuming you have the complete 2025 calendar year in Dates), which would give you a BLANK result.

       

      Normally these functions are used in the context of days or months within a single year, and will return a YTD value within that year.

      • dgwilson's avatar
        dgwilson
        Icon for Resolver III rankResolver III
        8 years ago

        Great answer. Thank you. Exactly explains why my result was blank too.

         

        My dates table extends many years beyond today... I’m hoping it’s a bit of future proofing.

        what syntax do you recommend in that totaltyd function that can use the date table and yet make the results relative to today? ... and I guess not break the function if a page filter removes all future dates and back some months...

         

        - David