Power Pivot Principles: Introducing the CLOSINGBALANCEMONTH Function
2 October 2018
Welcome back to our Power Pivot blog. Today, we introduce the CLOSINGBALANCEMONTH function.
CLOSINGBALANCEMONTH is a time intelligence function similar to the DATESYTD (you can read about the DATESYTD function here). The CLOSINGBALANCEMONTH function, like all time series functions, requires contiguous dates to work properly.
The CLOSINGBALANCEMONTH has the following syntax:
CLOSINGBALANCEMONTH(<expression>,<dates>[,<filter>])
The CLOSINGBALANCEMONTH function can be used to generate balance sheet values, as we want to view the inventory balance at the end of a period.
We have the following inventory table containing T-Shirt data from the months of January 2017 to June 2017. Do note, for simplicity’s sake our example table already contains contiguous dates.
After importing the table into Power Pivot let’s create a measure using the CLOSINGBALANCEMONTH function:
The next step is to create the following PivotTable:
The PivotTable now displays the inventory closing balance amount at the end of the month. From here we can use these values for our financial reports.
That’s all for this week!
Stay tuned for our next post on Power Pivot in the Blog section. In the meantime, please remember we have training in Power Pivot which you can find out more about here. If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.
