none
PowerPivot issue: Can't get PREVIOUSYEAR to work properly.

    Question

  • I have an issue where I can't get PREVIOUSYEAR, PREVIOUSMONTH functions to work.  At first I didn't have a separate table with a 'date dimension', but I created that using the great tutorial on creating a datetime table for PowerPivot and added it as a table, related it to my main sales table (date-->date).

    The formula is: PrevYr:=CALCULATE(SUM('Query'[ReportingUSD]),PREVIOUSMONTH('Calendar'[Date]

    All I get though, is blanks.

    Anyone have any advice/insights?

    Wednesday, March 21, 2012 2:53 PM

Answers

  • I can, but I'd have to mask an awful lot.  In reading more about this, I noticed the 'mark as date table' feature, which has been mentioned as being key to getting this stuff to work - but its greyed out when I select my date table in the layout view of PowerPivot.

    Anyone know why?

    The 'Mark as Date Table' button is always disabled in 'Diagram View'.

    You need to switch to 'Data View' before you can use it.

    Tuesday, April 10, 2012 12:58 PM

All replies

  • After the previousmonth function, try adding the ALL function:

    calculate(sum('Query'[ReportingUSD]), PreviousMonth('Calendar'[Date]), All('Calendar') )




    Javier Guillen
    http://javierguillen.wordpress.com/

    Wednesday, March 21, 2012 6:51 PM
  • Thanks for the reply Javier.  I tried that, and it still shows as blank. 

    Edward

    Wednesday, March 21, 2012 7:21 PM
  • Can you post a sample of your data? 



    Javier Guillen
    http://javierguillen.wordpress.com/

    Thursday, March 22, 2012 12:36 AM
  • I can, but I'd have to mask an awful lot.  In reading more about this, I noticed the 'mark as date table' feature, which has been mentioned as being key to getting this stuff to work - but its greyed out when I select my date table in the layout view of PowerPivot.

    Anyone know why?

    Thursday, March 22, 2012 5:30 PM
  • I can, but I'd have to mask an awful lot.  In reading more about this, I noticed the 'mark as date table' feature, which has been mentioned as being key to getting this stuff to work - but its greyed out when I select my date table in the layout view of PowerPivot.

    Anyone know why?


    Could you please just show top 10 rows records of your table to us? To this issue if yours, it seems it is relevant to your table. You can refer to this link to see soem reference about its rules http://www.powerpivotblog.nl/powerpivot-ctp3-what-is-new-for-time-intelligence-functions

    Challen Fu

    TechNet Community Support

    Friday, March 23, 2012 9:43 AM
  • I can, but I'd have to mask an awful lot.  In reading more about this, I noticed the 'mark as date table' feature, which has been mentioned as being key to getting this stuff to work - but its greyed out when I select my date table in the layout view of PowerPivot.

    Anyone know why?

    The 'Mark as Date Table' button is always disabled in 'Diagram View'.

    You need to switch to 'Data View' before you can use it.

    Tuesday, April 10, 2012 12:58 PM