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
Anonymous
Not applicable

YTD calculation not stoping at current month

Hi everyone, I´m having several problems with the YTD calculation:

 

1- The YTD Measure in this budget table that I created in excel is not stoping at current month (July 2021 or June 2021 if I want to see last closed month YTD comparison. For your reference, in the first table 1 shown below, the actual YTD amount should be: 68 940 270 for the january-june period

2- I´m trying to connet the budget excel Table that I explained in the previous point with a dynamic sales SQL table that is feeding the actual sales on a daily basis but when I try to make a comparison with the YTD actual data vs the budget, the interaction between both tables is not working. Could it be because the budget have some locations that are not actually opened and this is creating a problem with the actual data?

 

 

Capture 1.JPG

 

Capture 2.JPG

1 ACCEPTED SOLUTION
aj1973
Community Champion
Community Champion

Hi @Anonymous 

The formula is wrong, 

aj1973_0-1626115583247.png

There should be the name of the table that's it, in your formula you are referring to a Column. Delete ".[Date]" 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

View solution in original post

9 REPLIES 9
V-lianl-msft
Community Support
Community Support

Hi @Anonymous ,

 

There are limitations on time intelligence features in direct query mode. As @aj1973  said, please create a calendar table and use time intelligence function according to calendar table.

basically the build-in Time Intelligence functions are syntax sugar for a combination of other DAX functions.

Here you will find a lot of examples how to rewrite these build-in functions:

https://www.daxpatterns.com/time-patterns/ 

 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Ashish_Mathur
Super User
Super User

Hi,

Something like this should work.  Create a Date Table and build a relationship from the Fecha column of the Data Table to the Date column of the Calendar Table.  Write this measure

=calculate([Total PPTO],datesbetween(Calendar[date],date(year(today()),1,1),today()))

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hello, I have tried your formula but somehow it's still not working. Hereafter I send you the formula and the Calendar screenshots to see if you can guide me. Much appreciated!

Thanks

 

PPTO YTD calculation.JPG

Calendar.JPG

aj1973
Community Champion
Community Champion

Hi @Anonymous 

The formula is wrong, 

aj1973_0-1626115583247.png

There should be the name of the table that's it, in your formula you are referring to a Column. Delete ".[Date]" 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Anonymous
Not applicable

Hello @aj1973 , I just changed deleted the "Date" so it will reference the table as you mentioned but it´s not working. Is there anything else I´m doing wrong?

 

Capture YTD PPTO.JPG

aj1973
Community Champion
Community Champion

Hi @Anonymous 

Something is not right with the names!! Did you add a Calendar Table to your model?

Is there a possibility that you can share your file? Can't speculate on thematter like this, Sorry.

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Anonymous
Not applicable

Hi @aj1973 , how can I send both files to you? The calendar and the PPTO

I do not have the option to send it to you in here.

aj1973
Community Champion
Community Champion

Hi @Anonymous 

You said "both files"?? aren't they in one Pbix file?

 

well you can use Online services like Onedrive, dropbox , googledrive...

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

aj1973
Community Champion
Community Champion

Hi @Anonymous 

TOTALYTD is a Time intelligence function, therefore you need a Calendar Table in your model in order to use correctly. 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

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.