Not understanding TOTALYTD

[mhStein]

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 have imported an Excel list into Power BI in which only one date column and the corresponding sales are stored.

Today is the 24.06.

In my opinion, the measure

TotalYTD = TOTALYTD(SUM(Table1[Sales]),Table1[Date])

should show a total of 80. However, 90 is displayed. There are no filters in the report.

Can anyone help me?

[Anonymous · Accepted solution]

Hi @mhStein 

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!

[mhStein]

Hello,

Thank you very much!

Now I have finally understood it.

My wrong thought was actually that I was referring to today's date and not the context.

Greetings

Michael

[rajendraongole1]

Hi @mhStein - 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!!

[mhStein]

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]

Hi @mhStein -  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!!

[mhStein]

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]

Hi @mhStein - 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!!