Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Not understanding TOTALYTD

Hello, I am wondering whether I have misunderstood the TOTALYTD function.   I thought it only calculated values in an expression that are in the range from 01.01. of this year to today's date. I ...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous 

     

    You can add the Date column into the same table visual as the TotalYTD measure. Observe the result of each date, I'm sure this will help you understand how TOTALYTD function works better.

     

    The "date" of "year-to-date" is not today, it refers to a specified date in the context where the measure is evaluated. In the following data, there are two years data in the table. On each row, the specified date is the date on that row and the measure calculates the TYD result based on that date. 

    In your original table visual which only has the measure, the specified date will be the maximum date available in the data table, so it calculates YTD based on the date 02.07.24 and returns the result 90. 

     

    You may need to learn about the concept of Context. It is very important in DAX. The context of a measure may change so it will return different results when evaluated in different visuals or along with different fields. 

    Understanding context transition in DAX - SQLBI

     

    Best Regards,
    Jing
    If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

rajendraongole1's avatar
rajendraongole1
Icon for Super User rankSuper User
2 years ago

Hi Anonymous - Can you try below measure for total YTD

 

 

 

or with TotalYTD:

TotalYTD =
CALCULATE(
    TOTALYTD(
        SUM(_TotalY[Value]),
        _TotalY[Date]
    ),
    FILTER(
        _TotalY,
        _TotalY[Date] <= TODAY()
    )
)

 

 
Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hello,

thanks for your effort.

Yes, that works. I had already thought about workarounds.

I just don't understand why the function doesn't work on its own and why I need a filter.

That's why I'm wondering if I've misunderstood the function.

Greetings

Michael

  • rajendraongole1's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    2 years ago

    Hi Anonymous -  Glad that you found works, Can you please mark this as a solution.

     The TotalYTD function should work on its own without additional filters, provided the date table is correctly set up and recognized by Power BI. 

    The TOTALYTD function is specifically used for calculating year-to-date totals. It inherently considers the date context applied in your report or visualization.TOTALYTD calculates the year-to-date (YTD) total for an expression over a given time period. It is commonly used in time intelligence calculations to aggregate data from the start of the year to the last date in the current context.

     

    More info:

    TOTALYTD function (DAX) - DAX | Microsoft Learn

     

    Did I answer your question? Mark my post as a solution! This will help others on the forum!
    Appreciate your Kudos!!

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hello,

      Thanks again for your efforts.

      But my aim was to understand why the function does not work. I didn't need a workaround.

      Even with a date table, which I have marked accordingly, the function alone does not work.

      I just want to understand why this is not the case.

      Greetings

      Michael

      • rajendraongole1's avatar
        rajendraongole1
        Icon for Super User rankSuper User
        2 years ago

        Hi Anonymous - Please go through the similar thread more information about TOTALYTD

         

        Solved: How to use TOTALYTD command for compute YTD ? - Microsoft Fabric Community

         

        Quick bite:

        The TOTALYTD function in DAX is used to calculate the year-to-date total of an expression, such as a sum of sales, up to a specified date. This function takes into account the date context to aggregate the values from the start of the year to the specified date.

         

        Syntax:

        TOTALYTD(
        <expression>,
        <dates>,
        [<year_end_date>],
        [<filter>]
        )

         

        some reference link:

        TOTALYTD function (DAX) - DAX | Microsoft Learn

         

        Did I answer your question? Mark my post as a solution! This will help others on the forum!
        Appreciate your Kudos!!