PDA

View Full Version : Pivot Table: Difference From ... question



Paul_Hossler
11-28-2015, 07:12 PM
Simplified example in attachment and screen shot

Stock, Date, Closing as columns

The pivot table has the Date as a Report filter, Stock as Row, Closing as Data .. so far so good

If I add Close again as a Data, and want to show Values as difference from the previous date's close, I get an error

Is there a workaround. I would really prefer to use the PT capability as much as possible (i.e. no helper columns if possible) since the list of stocks and dates will change

Aflatoon
11-30-2015, 01:46 AM
Not with a regular pivot table. If you have Power Pivot you can do it, but for a 'Difference from' calculation in a normal pivot, you would need to have the Date as a row field and you have to have the previous date showing.

Paul_Hossler
11-30-2015, 07:19 AM
Not with a regular pivot table. If you have Power Pivot you can do it, but for a 'Difference from' calculation in a normal pivot, you would need to have the Date as a row field and you have to have the previous date showing.


Ahhh - row field. That's why some PTs work and this one doesn't'. :banghead:

OK, for extra credit :think: what would a helper column have for a formula that would work with gaps in the dates to return the previous entry??

Thanks

Aflatoon
11-30-2015, 08:07 AM
If you can sort the data as it is in your example, it's simply a subtraction of the cell above, assuming col A matches.

Paul_Hossler
12-01-2015, 07:35 AM
If you can sort the data as it is in your example, it's simply a subtraction of the cell above, assuming col A matches.


My example was a little too neat :think:

It's really sorted by Date, Account, Stock and it grows a bit longer each time I check how much money I've lost. There's also a lot of other columns

I could sort it by Stock and Data, do the math and re-sort it to Date, Account, Stock

Since I can't get the PT to do it,:crying::crying: I'll add a column and look backwards to find the previous date for each stock

Aflatoon
12-01-2015, 07:39 AM
You don't have any of the Power BI tools then?

Paul_Hossler
12-01-2015, 02:11 PM
You mean

https://powerbi.microsoft.com/en-us/

?

If so, I'll read up on it and experiment

Aflatoon
12-01-2015, 02:55 PM
I mean Power Pivot and/or Power Query really.