Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 @Anonymous
The formula is wrong,
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
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.
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.
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
Hi @Anonymous
The formula is wrong,
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
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?
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
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.
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
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
117 | |
101 | |
71 | |
61 |