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 y...
Need Help
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.

davidell's avatar
davidell
Frequent Visitor
8 years ago

Hi again.

Just an update.

I kinda of got the problem solved by using this code, where the .[Date] made the difference

YTD = TOTALYTD(SUM('Fact Orders'[OrderIntake_Lcy]);'Dim Calendar'[Date].[Date];ALL('Dim Calendar'[Date]);"4/30")

Since I'm working with fiscal year I've specified "4/30" as my year end.

However I'm going to show YTD in a matrix with year as a column, but it only shows YTD values for the 2016 and not from may to may.

As illustrated below, the right YTD is the one seen in the bar chart for april (130.726). However in the matrix its 100.411. How can I show the correct YTD numbers in the matrix?



Thank you very much

  • bsas's avatar
    bsas
    Post Patron
    8 years ago

    davidell

     

    You should look which date column are you using in matrix. Or you can try to add filer on your matrix view with start and end date for your fiscal year.

    • davidell's avatar
      davidell
      Frequent Visitor
      8 years ago

      Hey bsas,

      Thank you for your reply. No it does not solve the problemfiltering on date.

      When I in the matrix select 2015 and 2016, it produces two columns with total value for 2015 and 2016, in stead of adding it up to fiscal year.



      • davidell's avatar
        davidell
        Frequent Visitor
        8 years ago

        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