TotalYTD weird behaviour

[cbhh76]

Hello all,

I ran into a weird behaviour of TOTALYTD

.not adding to YTD from September even though there are values in the following months

I used the following measure for the YTD column:

YTD Forecast = TOTALYTD([Forecast];Period[DateToUseInMeasures].[Date])

I have no idea why it just stops adding the values from September onwards.

No date filter set.

Any hints appreciated. Thank you!

Regards

Christian

[AlB]

Hi @cbhh76 

I would try to avoid the Auto date  (.[Date]) feature and work with my own calendar table instead. The auto date time is prone to causing (apparently) weird behavior.

Another thing that I find weird is that the total for the measure is the same as the total for the TOTALYTD, so perhaps there's something wrong with that to start with?

Can you share the pbix? or at least show the code for the measure and the tables involved?

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  

[cbhh76]

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])

[cbhh76]

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.

https://ufile.io/c0ypbksq

Regards

Chri

[AlB]

@cbhh76 

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  

[cbhh76]

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

[AlB]

@cbhh76 

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  

[cbhh76 · Accepted solution]

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