none
SSAS Tabular - YTD calculation

    Question

  • Hi,
     
    I need help in calculating YTD in SSAS Tabular Analysis.
     

    1) Here is my Table name "DIM_TIME" and column values.

    DIM_TIME_ID (20010101, 20010102)  
    FULLDATE  
    DATENAME (1/1/2001, 1/2/2001)   
    DAYOFWEEK (2, 3)  
    DAYNAMEOFWEEK (Tuesday,Wednesday)   
    DAYOFMONTH (2, 3)   
    DAYOFYEAR (2,3)  
    WEEKDAYWEEKEND (weekday)   
    WEEKOFYEAR (1,1)  
    MONTHNAME (January, February)  
    MONTHNUM (1,1)  
    ISLASTDAYOFMONTH (N)   
    CALENDARQUARTER (1,2)  
    CALENDARYEAR (2001, 2002)  
    CALENDARYEARMONTH (2001-01, 2001-02)  
    CALENDARYEARQTR (2001Q1, 2001Q2)  
    MONTHKEY (Y, N)  
    WEEKKEY (Y,Y)

    2) TEB.jpg is my Fact column I want to use it in YTD.

    3) how to define a YTD by adding it in the calculated column by using my DIM_TIME table and its columns??
     

    Please someone help me with the expression based on my DIM_TIME table and its columns.

    This is the expression I am using:

    "=TOTALYTD(SUM([TEB]), 'DIMTIME' [CALENDARYEAR])"

    and I am getting this "Warning    1    Calculated column 'DIMTIME'[CalculatedColumn1] : A column specified in the call to function 'DATESYTD' is not of type DATE. This is not supported." Error.

    Thanks in Advance.




    • Edited by NewMSSAS Monday, October 07, 2013 11:48 PM
    Monday, October 07, 2013 10:11 PM

Answers

All replies

  • In the TotalYTD function, you need to specify a column of data type Date in the second parameter - CALENDARYEAR contains integer year values from what you've shown, so I guess FULLDATE is the column you want to use instead. Also, I think you will want this as a measure and not as a calculated column so you can use it in a PivotTable - if you create it as a calculated column, you'll get meaningless results when you try to sum the values up.

    Something like this should work (remember to paste this in the measure grid at the bottom of the screen, where you can see the measure Sum of TEB, and not as a new column on the table):

    TEB YTD:=TOTALYTD(SUM([TEB]), 'DIMTIME'[FULLDATE])

    Also, check that you have marked your date table as shown here:

    http://technet.microsoft.com/en-us/library/hh758415.aspx

    FULLDATE should be the unique identifier if it is indeed a date column.

    HTH,

    Chris


    Check out my MS BI blog I also do SSAS, PowerPivot, MDX and DAX consultancy and run public SQL Server and BI training courses in the UK

    Tuesday, October 08, 2013 9:13 AM
    Moderator
  • Thanks for the response chris.

    I have done a mistake while creating the connections, I have created single connections for each tables. So I had to give the Password again and again until it starts deploying. Any problem will be there because of this?

    But my issue here is: I am not getting any data when i check it in excel not in pivot.

    1) I have placed the TEB YTD:= in bottom of TEB in my Fact table not in DIM_TIME table.

    2) I do not have any error in tabular model and the deployment is successful. What might be my issue here?



    • Edited by NewMSSAS Tuesday, October 08, 2013 6:44 PM
    Tuesday, October 08, 2013 6:36 PM
  • You won't have any problems with multiple connections apart from having to enter the password lots of times; also, if you need to alter your connection in the future, you'll have to do it lots of times rather than just once. I would advise deleting tables/connections now and starting again, importing everything from a single connection.

    What do you mean by "excel not in pivot."? Do you see data in the PivotTable when you query your model in Excel?

    Chris


    Check out my MS BI blog I also do SSAS, PowerPivot, MDX and DAX consultancy and run public SQL Server and BI training courses in the UK

    Tuesday, October 08, 2013 7:59 PM
    Moderator
  • I will change the connections as said.

    When I query my model in excel I am not getting any values for TEB YTD in Pivot Table. But If i create a measure of SUM of TEB its working.

    sAlso, Please see this:

    • Edited by NewMSSAS Tuesday, October 08, 2013 8:30 PM
    Tuesday, October 08, 2013 8:24 PM
  • I suggest trying the following: put FULLDATE on rows in your PivotTable, and put your Sum of TEB measure on columns; then, put your TEB measure next to Sum of TEB. Do you see anything?

    Chris


    Check out my MS BI blog I also do SSAS, PowerPivot, MDX and DAX consultancy and run public SQL Server and BI training courses in the UK

    Tuesday, October 08, 2013 8:27 PM
    Moderator
  • That is working....Thank You.

    But I have one question. Sorry for asking this if it is not the right one. This is for my understanding.

    Placing the TEB YTD:= in DIM_TIME table is correct or FACT_SALES_W table is correct.

    That is under FULLDATE column is correct or TEB column in fact table is correct?

    Tuesday, October 08, 2013 8:42 PM
  • Actually, it doesn't matter where you put your measure definition - it will work just the same regardless of the table you put it in, and the column you put it under. My advice is to have a system for organising your measures and commenting them so that you can find your measures easily. See

    http://cwebbbi.wordpress.com/2013/05/15/comments-and-descriptions-in-dax/

    for more details on this.

    Regards,

    Chris


    Check out my MS BI blog I also do SSAS, PowerPivot, MDX and DAX consultancy and run public SQL Server and BI training courses in the UK

    Tuesday, October 08, 2013 9:07 PM
    Moderator
  • Thats awesome Chris. I am new to this tool and your input helped me a lot.

    Also I have been following your blog from couple of days.

    Tuesday, October 08, 2013 9:16 PM
  • Chris,

    I am using the below expression for Previous YTD:

    TEB PREVIOUSYTD:=CALCULATE(SUM([TEB]), SAMEPERIODLASTYEAR('DIM_TIME'[FULLDATE]))

    Is this the correct expression??

    Also, I want to know the expression for Previous QTD??

    Can you please help me in this?

    Wednesday, October 09, 2013 11:49 PM
  • The easiest way of finding the YTD value for the same period in the previous year is to reuse your existing measure in the new expression. So if your YTD measure for the current year is called

    TEB YTD

    You can use the expression

    TEB PREVIOUSYTD:=CALCULATE([TEB YTD], SAMEPERIODLASTYEAR('DIM_TIME'[FULLDATE]))

    to get the value of TEB YTD for the same period in the previous year. If you create a QTD measure for the current year using the TOTALQTD() function, you can use the same technique to get the QTD for the same period in the previous year.

    Chris


    Check out my MS BI blog I also do SSAS, PowerPivot, MDX and DAX consultancy and run public SQL Server and BI training courses in the UK

    Thursday, October 10, 2013 8:07 AM
    Moderator
  • Got that chris. I have created both PYTD and PQTD.

    So for my understanding

    TEB PREVIOUSYTD:=CALCULATE([TEB YTD], SAMEPERIODLASTYEAR('DIM_TIME'[FULLDATE])). This expression gives the desired result: 

    If for YTD I have values like Jan 1, 2013 to Oct 9, 2013

    My PreviousYTD should be Jan 1, 2012 to Oct 9, 2012. Is this correct?

    Also, I saw the below expression in one of the website:

    =CALCULATE(sum(Table1[sales]),
                      DATESBETWEEN(datum[Date],
                                      FIRSTDATE(DATEADD(datum[Date],-12,MONTH)),
                                      LASTDATE(DATEADD(Table1[Date],-12,MONTH))
                      ))

    Is this the same expression for calculating the PYTD?? Does it give the same result set for us??

     

    Thursday, October 10, 2013 6:00 PM
  • Yes, correct, the calculation should work the way you've described. Using DATESBETWEEN will do exactly the same thing too.

    Chris


    Check out my MS BI blog I also do SSAS, PowerPivot, MDX and DAX consultancy and run public SQL Server and BI training courses in the UK

    Thursday, October 10, 2013 6:44 PM
    Moderator
  • Thanks Chris.

    Can you tel me the expression for 'Rolling 13 weeks' also based on my table and columns? 

    Thursday, October 10, 2013 6:50 PM
  • It will depend on how exactly you define what a week is. What happens at the start and end of a year? Can a week cross over two years, or is a week only ever in one year? Is it enough for your calculation to get the sum of the last 91 days?

    Chris


    Check out my MS BI blog I also do SSAS, PowerPivot, MDX and DAX consultancy and run public SQL Server and BI training courses in the UK

    Friday, October 11, 2013 7:26 AM
    Moderator