Forum Discussion

davidell's avatar
davidell
Frequent Visitor
8 years ago

YTD time intelligence wrong numbers-Odd behavior?

Hi PBI community,

I have encounted a problem with performing simple time intelligence functions on dataset. I’ve crawled the internet for a solution sadly without any luck, where as I hope some of you guys might point me in the right direction.


The problem is quite simple. I want to create a simple YTD calculation, which shows the YTD of order intake in a matrix visualization. No matter how I define the calculated measure it won’t work. Some how it seems like that time intelligence does not know, which year it should start counting from in the YTD calculation? 


Setup:

*Connected to a SQL server.

*Contains a Date table (Dim Calendar) which is in a relationship with Fact Orders through many to one and a cross filter direction as single.
*The Calendar_key from Dim Calendar is linked with OrderDate_key from Fact Orders.
* The Date column is marked as Date with dd-mm-yyyy.


Used calculated measures:

I’ve tried the following calculated measures:

YTD: = TOTALYTD(SUM('Fact Orders'[OrderIntake_Lcy]);'Dim Calendar'[Date]; FILTER('Dim Calendar'[Date])
 
YTD: = CALCULATE(SUM ('Fact Orders'[OrderIntake_Lcy]); DATESYTD ('Dim Calendar'[Date]))
 
YTD: = TOTALYTD(SUM('Fact Orders'[OrderIntake_Lcy]);'Dim Calendar'[Date]; ALL('Dim Calendar'[Date]); "09/25")
 
YTD: = TOTALYTD(SUM('Fact Orders'[OrderIntake_Lcy]);'Dim Calendar'[Date]; filter('Dim Calendar';[Date] <NOW()))

However none of them produced the right result. As a comparison I’ve tried DATESBETWEEN with a start and end date, which shows the right numbers:

 

DatesBetween = CALCULATE(SUM('Fact Orders'[OrderIntake_Lcy]); DATESBETWEEN('Dim Calendar'[Date];
   DATE(2016;01;01);
   DATE(2016;9;25)
))

 

Output:
When I compare both the results from the YTD calculation and the DATESBETWEEN I can see in the matrix visualization that the YTD calculated measures counts all months in a year, where as the DATESBETWEEN stops at the specified end year, as it should and shows the right result. The YTD calculated measures does not stop at the current date.

 

In the example below I use with and YearEndDay, which is sat to 25/09.
YTD = TOTALYTD(SUM('Fact Orders'[OrderIntake_Lcy]);'Dim Calendar'[Date]; ALL('Dim Calendar'[Date]);"25/09")



Does anyone know what can be causing the problem with YTD. Why does it not pick up the right dates and produce the right numbers?

Thank you very much for your time

Regards,

David

Need Help

8 Replies

  • bsas's avatar
    bsas
    Post Patron
    8 years ago

    Hi davidell,

     

    You need to use MIN and MAX in calculate. E.G Calculate(SUM([sales]), Filter(table, and( your end date <= Max(alldates.[date]), your end date >= Min(alldates.[date]))))

     

    or 

     

    total sale during 2015/1/1 to 2015/7/20 = CALCULATE(SUM(Sales[SALE]),AND(Sales[DATE]<=DATE(2015,7,20),Sales[DATE]>=DATE(2015,1,1)))

     

    Also you can searc in YTD solved problems here. There are a lot of similar topics.

    • davidell's avatar
      davidell
      Frequent Visitor
      8 years ago

      Hi bsas,

      Thank you very much for your reply.

      I've tried what you said and it works out. However I want the YTD to automatically detect the current year and compare with other years.

       

      Basically I want to create a matrix in which I can compare YTD, LY and change between YTD and LY across different dates such as 2010,2011,2012,...2018. 

      Since I'm not a DAX jedi yet, I kind of lack the insight in DAX.

      I've tried searching the forum and found many similar problems, however none of them which I could get to work:


      /David


      • bsas's avatar
        bsas
        Post Patron
        8 years ago

        davidell

         

        If you wanna detect current year then use Year(Today()) in your calculations. I'll look for a solution in the evening.