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?

Answer 1

After the previousmonth function, try adding the ALL function:

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

---

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

Answer 2

Thanks for the reply Javier.  I tried that, and it still shows as blank. 

Edward

Answer 3

Can you post a sample of your data? 

---

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

Answer 4

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?

Answer 5

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

Answer 6

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.