How to use Power Pivot

[jak82]

Right were basically finding the pivot tables in excel are not up to scratch when dealing with large amounts of data.

We have been told that power pivot utilizes the processors better. (Going to have to learn DAX)

If I have the year column which shows 2015 2016 how can I add a column which minuses the 2015 data from 2016.

The column with 2015 2016 is called 'year' I need to add a column which subtracts.

Thanks

Chris

 

[scottsen]

It is going to depend on the particular model, and you will probably want to totally separate calendar table, and really you should grab Matt or Rob's book... but in general, you will have something like this:

Total Sales := SUM(Sales[Amount])
Total Sales - PY := CALCULATE([Total Sales], PREVIOUSYEAR(Calendar[Date]))
Total Sales - Delta := [Total Sales] - [Total Sales - PY]

 

[jak82]

Thanks Scott Just noticed this, will have a shot

What does the Calender Date refer to? I have referenced this to the insertdate column in my table and it seems to be coming up blank?

Thanks

 

[jak82]

In my total sales I have

Total Sales:=SUM([Column3])

Total Sales: 12345678.069915

in the previous year I have

Total Sales Past Year:=CALCULATE([Total Sales], PREVIOUSYEAR(People[dateins]))

Total Sales Past Year: (blank)

For some reason it is not picking up the previous year.

Thanks

 

[Matt Allington]

You are using a standard time intelligence function. For it to work, you must follow the rules. Read about them at my blog here. Power Pivot Calendar Tables - Excelerator BI