cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rtoledo1980
Frequent Visitor

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 @rtoledo1980 

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 @rtoledo1980 ,

 

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/

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 @rtoledo1980 

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

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 @rtoledo1980 

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

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 @rtoledo1980 

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 @rtoledo1980 

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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!