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

    Pregunta

  • 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?

    miércoles, 21 de marzo de 2012 14:53

Respuestas

  • 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.

    martes, 10 de abril de 2012 12:58

Todas las respuestas

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

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




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

    miércoles, 21 de marzo de 2012 18:51
    Usuario que responde
  • Thanks for the reply Javier.  I tried that, and it still shows as blank. 

    Edward

    miércoles, 21 de marzo de 2012 19:21
  • Can you post a sample of your data? 



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

    jueves, 22 de marzo de 2012 0:36
    Usuario que responde
  • 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?

    jueves, 22 de marzo de 2012 17:30
  • 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

    viernes, 23 de marzo de 2012 9:43
    Moderador
  • 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.

    martes, 10 de abril de 2012 12:58