Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Joachim2108
Helper I
Helper I

Total for Sales YTD measure is incorrect

Hi all,

 

I have a calculated measure for YTD and YTD last year presented in a table. The issue is that the totals are incorrect (for last year's YTD), as seen on the picture below, however the chart shows the correct values. I also want to present the last years YTD per customer, but then these values also show up as incorrect when comparing it to the numbers in the data source, and I assume the solution to this problem is the same as the latter.

 

YTD_Help.png

 

Measures used:

YTD Last year = CALCULATE([YTD];SAMEPERIODLASTYEAR('DATE'[DATE]))

YTD = TOTALYTD([SUM OF REVENUE]; DATE[DATE])
SUM OF REVENUE = SUM('SALES_TABLE[REVENUE])
 
I need the values to be shown correctly so I can make further calculations. I know the issue with incorrect totals is a common issue, but I can't seem to find a solution to my exact situation. Any help would be much appreciated! Thanks in advance.
3 REPLIES 3
amitchandak
Super User
Super User

@Joachim2108 , Try last year YTD like

 

Last YTD Sales = CALCULATE(([SUM OF REVENUE],DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

 

make sure date table is marked as date table

Hi @amitchandak , the table is indeed marked as the date table. I have also tried the alternative DAX query, however it gives me the same result. the YTD for individual dates are correct, however the total sum is incorrect - same thing when presented in a card visual for example.

I'm quite new to DAX, but I've seen others with similar issues solve the problem using VAR in their statements, which I'm still trying to understand. Hopefully that can give an indication of what to do.

Thanks in advance for any help!

Hi @Joachim2108 

I guess if you misunderstood the concept of YTD and the total sum? YTD is a cumulative total from the start of a year to a specific date point. If you want to show the correct YTD values in the table, you need to include date or month column in the table to tell the measure which date or month is the end point for the sum. You can convert your line chart into a table visual to check the result. I think it will show the correct YTD values.

 

Kindly let me know if you have any questions so that we can investigate a proper solution further.

Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.