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 

     

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

AlB's avatar
AlB
Community Champion
6 years ago

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  Datanaut

  • cbhh76's avatar
    cbhh76
    Regular Visitor
    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