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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
tbui
New Member

Strange behaviour with YTD calculations

Hi,

 

I'm trying to do some YTD calculations in one of my summaries. I did it in 2 different ways, each came with its own problem:

 

1. At first, I tried using the TotalYTD function:

YTD Sales Alt = TOTALYTD(sum(SalesHistoryData[Sales]),DATESYTD(CalendarTable[Date],"30/06"))

I know the format string is dependent on the local setting of your machine, but my machine setting is exactly that "dd/mm", but somehow that formular doesn't work. It does the YTD sum, but it runs from Jan to Dec rather than Jul to Jun as I told it to.

 

2. And I know that format string is correct because I tried it with a different formula:

YTD Sales = Calculate(sum(SalesHistoryData[Sales]),DATESBETWEEN(CalendarTable[Date],STARTOFYEAR(CalendarTable[Date],"30/06"),LASTDATE(CalendarTable[Date])))

That gives to expected behaviour in terms of summing from Jul to Jun. However, it also gives a sum of everything as well:

 

(there are no blanks in my calendar table and here's the summary without putting the 2nd measure):

image.png

(but as soon as I put in the 2nd measure, suddenly there is a blank in the summary)

image.png

 

Can I get some help please so that I can understand what is happening:

 1. How come the format string worked in the 2nd formula but not the 1st?

 2. Why did the 2nd formula summarise the total for the whole dataset against a "blank" date that doesn't exist?

 

Also, I thought these 2 formulas should be an exact substitute for each other (or at least they should behave in the same way), but apparently not. Can anyone please help by pointing out why they are behaving differently, should they be doing the same thing?

 

Thanks.

Tam.

0 REPLIES 0

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors