CODEWITHSUNDEEP
excelpowerpivot
Robert Arledge
Robert Arledge

Reputation: 41

Date intelligent column to keep running total of past 3 months in PowerPivot

I've been having trouble trying to get a "running total" of NOI for the past 3 months at any given point in time. Here's the set up of how it looks now (with T3 NOI supposed to be the past 3 month running total):

Date       | PropertyID | NOI  |T3 NOI
08/1/2013  | 1          | $5   | $5
09/1/2013  | 1          | $6   | $6
10/1/2013  | 1          | $8   | $8
11/1/2013  | 1          | $2   | $2
12/1/2013  | 1          | $3   | $3
01/1/2014  | 1          | $4   | $4
02/1/2014  | 1          | $5   | $5

As you can see, the T3 NOI is just a reflection of the NOI. Here is what it should look like:

Date       | PropertyID | NOI  |T3 NOI
08/1/2013  | 1          | $5   | $5
09/1/2013  | 1          | $6   | $11
10/1/2013  | 1          | $8   | $19
11/1/2013  | 1          | $2   | $16
12/1/2013  | 1          | $3   | $13
01/1/2014  | 1          | $4   | $9
02/1/2014  | 1          | $5   | $12

I'm using this formula in the T3 NOI column:

=CALCULATE(SUM(factGLBasis[NOI]), DATESINPERIOD(factGLBasis[Month],LASTDATE(factGLBasis[Month]),-3,MONTH))

Where factGLBasis is the name of the table and the [Month] column is our main date field signified by the first of each month of the year.

Upvotes: 2

Views: 601

Answers (1)

Robert Arledge
Robert Arledge

Reputation: 41

I finally figured it out. I was inputting my formula in the formula bar of a calculated column in PowerPivot. The correct way was to put in my formula in the calculation area, label it "T3 NOI" or whatever you want, and then use that field in the pivot table.

Upvotes: 2

Related Questions