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
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
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.

Top Solution Authors