Forum Discussion
TotalYTD weird behaviour
Thank you again, AlB
My initial pbix did't reflect the original as PLAN does have values for every month.
So I needed some sort of IF function.
I did a search on SUMIF equivalents in DAX and found this post here which was helpful
https://community.powerbi.com/t5/Desktop/SUMIF-Equivalent-in-DAX/td-p/230727
I then changed the Forecast Function to this
Forecast = SUMX(Tabelle;IF(Tabelle[ACT] = BLANK();CALCULATE(SUM(Tabelle[PL]));CALCULATE(SUM(Tabelle[ACT]))))
This solved the issue and gave both correct Totals and correct YTD.
So thank you again AlB and Thanks TomMartens
Thank you for your quick response.
When I try to use anything else, other than .[Date] like for example .[Year] or .[Month] I get an error. Not sure how to use a different calendar table here, as the one used in this measure is used in all other measure throughout the report.
Unfortunately I am not able to share the pbix.
The Forecast measure is
Forecast = IF([Produktion IST]>0;[Produktion IST];[Produktion Plan])
Hi again,
Thank you, AlB
I have recreated the behaviour in a seperate pbix.
The problem doesn't seem to be in the YTD function but rather in the way I created my Forecast measure.
However, this behaviour and the way the totals are calculated seem rather buggy.
I have uploaded the pbix and appreciate any help.
Regards
Chri
1. Time intelligence functions are only guaranteed to work with a full calendar table, with no gaps. You do not have that, hence the problems.
2. Like I said, I would strongly recommend using your own calendar table rather than the auto date time to avoid this kind of stuff. Just deactivate at Options-> Global -> Data load -> Auto date time for new files and/or
Options-> Current File -> Data load -> Time Intelligence ->Auto date time
Just create your calendar table and create the appropriate relationships
Please mark the question solved when done and consider giving kudos if posts are helpful.
Cheers
Thanks AlB
I did create my own calendar table in the pbix that I provided.
As I said, the error occures before the YTD function, so not sure if it is a timeintelligence problem. If you look at the forecast column, there is already an error in the Total
I did the changes you mentioned in the options but it had no effect.
Did you have a look at the pbix by any chance?
Thanks
Chris
That's not a calendar table. It has to have all days in the year. Otherwise time intelligence is not guaranteed to work properly
Anyway, I've just seen that the problem is the way you build the [Forecast] measure. When you're using it with TOTALYTD, the cumulative of [Plan] will always be non-zero so [Actual] never comes into the picture in the IF( ). That's why it stops accumulating at 80, it's the cumulative total for [Plan]. Try this instead. See the attached file
YTD Forecast2 = TOTALYTD ( SUM ( Tabelle[ACT] ) + SUM ( Tabelle[PL] ); 'Date'[Month].[Date] )
In any case, like I said before, I would refrain from using the auto date feature.
Please mark the question solved when done and consider giving kudos if posts are helpful.
Cheers
