Excel Forum

Pivot Table from power query table - Opening and Closing Stock

Diane Chisholm

New Member

Members

Power Query

Power Pivot

Power BI

Forum Posts: 2

Member Since:
June 29, 2017

Offline

August 7, 2021 - 7:38 pm

Hi,

I have a trading statement in pivot table.

Issue, the grand total sums the row, only want opening balance at beginning of the year to display in grand total/ total column, and same with closing stock.

I have provided an example of data below and display of pivot table.

Your assistance is appreciated. Frown

Pivot table has the following headings with row items:

Revenue items

Cost of Goods Sold items

Operational Expense items

Grand Total

In the Cost of Goods Sold items, i have a row for opening stock and a row for closing stock.

Monthly pivot

													2021 Total
	January	February	March	April	May	June	July	August	September	October	November	December
Opening Stock	3521	4000	3000	2000	3000	4000	0	0	0	0	0	0	3521
Closing Stock	-4000	-3000	-2000	-3000	-4000	-3000	0	0	0	0	0	0	-3000

Yearly pivot

	2020	2021
Opening Stock	2950	3521
Closing Stock	-3521	-3000

Data entered in worksheet:

Account Name	Transaction Date	Amount
Opening Stock	1/01/2021	3521
Closing Stock	31/01/2021	-4000
Opening Stock	1/02/2021	4000
Closing Stock	28/02/2021	-3000
Opening Stock	1/03/2021	3000
Closing Stock	31/03/2021	-2000
Opening Stock	1/04/2021	2000
Closing Stock	30/04/2021	-3000
Opening Stock	1/05/2021	3000
Closing Stock	31/05/2021	-4000
Opening Stock	1/06/2021	4000
Closing Stock	30/06/2021	-3000
Opening Stock	1/07/2021	3000
Closing Stock	31/07/2021	0
Opening Stock	1/08/2021	0
Closing Stock	31/08/2021	0
Opening Stock	1/09/2021	0
Closing Stock	30/09/2021	0
Opening Stock	1/10/2021	0
Closing Stock	31/10/2021	0
Opening Stock	1/11/2021	0
Closing Stock	30/11/2021	0
Opening Stock	1/12/2021	0
Closing Stock	31/12/2021	0
Opening Stock	1/01/2022	0
Closing Stock	31/01/2022	0