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?
Solved! Go to Solution.
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:
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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
Hope this helps.
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!
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?
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.
Check out the News & Announcements to learn more.
Congratulations, the new Super User Season 2 for 2021 has started!
Check out the new Power Platform Community Connections gallery!
Mark your calendars and join us for our next Power BI Dev Camp!