Pivot Table: Difference From ... question

[Paul_Hossler]

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]

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]

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]

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]

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]

You don't have any of the Power BI tools then?

[Paul_Hossler]

You mean

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

?

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

[Aflatoon]

I mean Power Pivot and/or Power Query really.