Forum Discussion

cbhh76's avatar
cbhh76
Regular Visitor
6 years ago
Solved

TotalYTD weird behaviour

Hello all,   I ran into a weird behaviour of TOTALYTD   . I used the following measure for the YTD column:   YTD Forecast = TOTALYTD([Forecast];Period[DateToUseInMeasures].[Date]) I hav...
Need Help
  • cbhh76's avatar
    cbhh76
    6 years ago

    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 

     

AlB's avatar
AlB
Community Champion
6 years ago

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  Datanaut

 

cbhh76's avatar
cbhh76
Regular Visitor
6 years ago

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's avatar
    cbhh76
    Regular Visitor
    6 years ago

    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's avatar
      AlB
      Community Champion
      6 years ago

      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  Datanaut

          

      • cbhh76's avatar
        cbhh76
        Regular Visitor
        6 years ago

        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