PowerPivot: Adding a Calculated Column - Beginning Period Stock Balance
Introduction
In this article, we will guide you through the process of adding a calculated column in PowerPivot, specifically the "Remaining at Beginning Period (Kg)" column, which will be summed up in the following steps.
Preparation
Assuming you have the source table "stock" in a workbook attachment.
Adding the Calculated Column
1. Open the PowerPivot window in Excel.
2. In the PowerPivot window, click on the "Table" menu, then select "New Table".
3. In the new table, define the relationship with the "stock" table using the "Relationships" tab in the PowerPivot window.
4. In the new table, add the following formula for the "Remaining at Beginning Period (Kg)" column:
CALCULATE(
SUM('stock'[Quantity]),
ALL('stock'),
'stock'[Date] < EARLIER('stock'[Date])
)
5. Save and close the PowerPivot window.
Summing Up the Calculated Column
1. In the Excel worksheet, create a new PivotTable using the "stock" table as the data source.
2. Add the "Remaining at Beginning Period (Kg)" column to the Values area of the PivotTable.
Code Explanation
The formula uses the CALCULATE function to sum up the "Quantity" column in the "stock" table, considering all rows (ALL function) except those where the date is later than the current row's date (EARLIER function).