Forum Discussion
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 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?
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!
7 Replies
- rajendraongole1
Super User
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!!- rajendraongole1
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!!
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!
