locked
What is best practice to restrict TOTALYTD? RRS feed

  • Question

  • My fact table has got sales data till 28.12.2011, my dimdate table has got datekey column till 31.12.2011. How can I write TOTALYTD to restrict PowerPivot columns (after adding datekey to column labels) with 28.12.2011 and not 31.12.2011?

     

    thank you!!!

    Monday, January 2, 2012 11:39 AM

Answers

All replies

  • Hi Stotsky

    TOTALYTD supports  and Year_End_Date.
     
    So, if we wanted to calculate the YTD for a financial year which end 28 december, the DAX would look something like:
     
    =TOTALYTD(SUM(FactSales[SalesAmount]), Time[Date], ALL(FactSales), “28.12.2011”)

     

    But maybe it's better to adjust your Time dimension and make 28.12.2011 the last day in 2011 and 29.12.2011 to year 2012

     


    Larra
    Wednesday, January 4, 2012 7:06 AM
  • Hi stotsky,

    You can filter some dimension members to only calculate till 28.12.2011, you can also make use filter and sum function to achieve this, please refer to this link about the similar requirement http://paultebraak.wordpress.com/2011/12/16/dax-ytd-on-445-calender-and-irregular-end-dates/ 

    Thanks,
    Challen Fu


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Challen Fu Thursday, January 12, 2012 12:19 PM
    Wednesday, January 4, 2012 8:22 AM
  • thank you! very much!

    Is it possible not to hard code "28.12.2011" in

    =TOTALYTD(SUM(FactSales[SalesAmount]), Time[Date], ALL(FactSales), “28.12.2011”)

    in essence, "28.12.2011" is max date from FactTable; I can't change Time Dimension cause for one Pivot Table I do not want to show extra days, but for second I need 30.12.2011 in December

    and one more question -

    is it difference in performance between TOTALYTD  and Sum&Filter approach like Challen Fu has said? 

    Wednesday, January 4, 2012 9:06 AM
  • You know PowerPivot is an in memory solution, which leverage Vertipaq engine, it have a good performance than original engine. Just to these two different function, there is very little difference at performance.

    Thanks,
    Challen Fu


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, January 9, 2012 3:15 AM