locked
A question about slicer & cumulative values RRS feed

  • Question

  • I have created a model in PowerPivot for Excel. I want to caculate some cumulative values such as Cumulative Sales and Prior Year Sales. The DAX expression is shown as below:

    Cumulative Sales:=CALCULATE(SUM([SalesAmount]), DATESBETWEEN(Dates[Date], FIRSTDATE(ALL(Dates[Date])), LASTDATE(Dates[Date])))

    Prior Year Sales:=CALCULATE(SUM([SalesAmount]), DATESBETWEEN(Dates[Date], FIRSTDATE(DATEADD(Dates[Date], -12, MONTH)), LASTDATE(DATEADD(Dates[Date], -12, MONTH))))

    Then I create a PivotTable report with year slicer in the same Excel file.

    The problem is when I click slicer, Cumulative Sales column will show SalesAmount of the selected year and Prior Year Sales column will be blank.

    The slicer can only pass the filtered values to the DAX expression???

    How to solve this problem?

    Friday, February 17, 2012 5:59 AM

Answers

  • I tried to download the file, but it doesn't work.... anyway, have you marked the calendar table as a Date table and set the corresponding date column? In SQL 2012 it should work just by fixing it.

    BTW: Prior Year might be simplified using PARALLELPERIOD, have you tried that?


    Alberto Ferrari
    http://www.powerpivotworkshop.com

    • Marked as answer by Bird007 Tuesday, February 21, 2012 1:17 AM
    Monday, February 20, 2012 7:32 PM

All replies

  • Which version of PowerPivot are you using? There are significant differences between the 1.0 version and the current one and on how to solve the usse.

    Moreover, as I often ask, if you can share the workbook, life in debugging it will be much easier. :)


    Alberto Ferrari
    http://www.powerpivotworkshop.com

    Friday, February 17, 2012 9:42 AM
  • Which version of PowerPivot are you using? There are significant differences between the 1.0 version and the current one and on how to solve the usse.

    Moreover, as I often ask, if you can share the workbook, life in debugging it will be much easier. :)


    Alberto Ferrari
    http://www.powerpivotworkshop.com

    Hi, Ferrari

    Thanks for your reply:-)

    I am using SQL Server 2012 RC0 PowerPivot for Excel 2010. I have uploaded the workbook to the following link:

    https://skydrive.live.com/redir.aspx?cid=67248b606b106e2b&resid=67248B606B106E2B!265&parid=67248B606B106E2B!235&authkey=!AHjE2iCfG8Tke-o

    Monday, February 20, 2012 3:50 AM
  • I tried to download the file, but it doesn't work.... anyway, have you marked the calendar table as a Date table and set the corresponding date column? In SQL 2012 it should work just by fixing it.

    BTW: Prior Year might be simplified using PARALLELPERIOD, have you tried that?


    Alberto Ferrari
    http://www.powerpivotworkshop.com

    • Marked as answer by Bird007 Tuesday, February 21, 2012 1:17 AM
    Monday, February 20, 2012 7:32 PM
  • Thank you very much! It works when I set my calendar table as a Date table :-)
    Tuesday, February 21, 2012 1:18 AM
  • Hi,

    I'm using Excel 2013 and I still have a similar problem. My calendar table has the following structure:

    QtrDate | Year | Qtr | Fiscal Period

    QtrDate has a Date data type, is unique and is contiguous with day granularity. I marked this table as "Date Table" and selected QtrDate as unique identifier.

    In my fact table I have a column called FiscalPeriod, and a calculated column called "QtrStartDate". I then created a relationship between this column and the QtrDate column in the Calendar table.

    Finally, I created the following metric:

    CumExtPrice:=CALCULATE(SUM([Extended Price]),FILTER( ALL(BMSCalendar), BMSCalendar[QtrDate]<=MAX(BMSCalendar[QtrDate])))

    Unfortunately, when I create a pivot table from this, and I add CumExtPrice as a column, I don't get cumulative values. Instead, I get Extended Price per Quarter.

    Any idea what I am doing wrong?

    Thanks for your help.

    Monday, April 15, 2013 4:38 AM