Forum Discussion

kingchad5's avatar
kingchad5
Helper 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.

kingchad5's avatar
kingchad5
Helper I
9 years ago

 

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

 

 

OwenAuger's avatar
OwenAuger
Super 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
    Resolver 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

    • Nischal's avatar
      Nischal
      Helper I
      8 years ago

      but i have set the date with respect to min/max even then i am getting blank ytd

  • kingchad5's avatar
    kingchad5
    Helper I
    9 years ago

    This did the trick.  I updated my Date table to reflect the MIN/MAX of dates in my data table.

     

    Thanks for your help.

  • ydaoud's avatar
    ydaoud
    Frequent Visitor
    7 years ago

    Very Useful, Thanks a lot 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Thank you soooo much. Problem Solved.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi, Ive filtered date in the formula, but it still returns BLANK. Do you know why?

    YTD $ = TOTALYTD([Fixed Budget $], DateTable[Dates], DATESBETWEEN(Horizon[Date], MIN(Horizon[Date]), MAX(Horizon[Date])))
    Expression 'Fixed Budget $' = SUM(Horizon[Fixed Budget USD])
    • OwenAuger's avatar
      OwenAuger
      Super User
      4 years ago

      Anonymous Just saw your reply to this post 

      Not sure if you have solved already.

       

      The problem with your measure is that you are applying two different date filters (on different columns as well), which might have no intersection, resulting in a blank result for the measure.

       

      Your original measure

      YTD $ =
TOTALYTD (
    [Fixed Budget $],
    DateTable[Dates],
    DATESBETWEEN ( Horizon[Date], MIN ( Horizon[Date] ), MAX ( Horizon[Date] ) )
)

      translates to

      YTD $ =
CALCULATE (
    [Fixed Budget $],
    DATESYTD ( DateTable[Dates] ),
    DATESBETWEEN ( Horizon[Date], MIN ( Horizon[Date] ), MAX ( Horizon[Date] ) )
)

       (see https://dax.guide/totalytd/)

       

      Could you describe the date filter that you are wanting to produce? Also, did you intend to filter both DatesTable[Dates] and Horizon[Date]?

       

      Regards,

      Owen