Issues with PowerPivot OPENINGBALANCEMONTH

Question

I am Experiencing problems with the DAX Time Intelligence Functions Sepecifically OPENINGBALANCEMONTH

I have set up a very simple Test File with just over 4 Months data in, Every date is representated and has 2 Records for each

vDate	Data
1/1/2012	8
1/1/2012	4
1/2/2012	9
1/2/2012	4
1/3/2012	2
1/3/2012	6
1/4/2012	4
1/4/2012	7

I have a Time Dimension Table and have created the relationship

I have defined two measures

Opening Bal: =OPENINGBALANCEMONTH(sum(Table1[Data]),FullDateRange[vDate])

Closing Bal: =CLOSINGBALANCEMONTH(sum(Table1[Data]),FullDateRange[vDate])

Closing Bal works fine - and gives the expected result, even at the month and qrtr level, Opening Bal only works at the vdate level and is Blank always at the higher levels- what am I missing here?

Answer 1

OK - Ill answer my own question

for some reason you have to use LASTDATE

on the date range to get the opening balance

=OPENINGBALANCEMONTH(sum(Table1[Data]),LASTDATE(FullDateRange[vDate]))

This appears to give the expected result, But note that because I have not value in for Apr end or June end, closing balance is blank

Answer 2

Hi Dave -

Looks like you're only missing it by a bit.  The BALANCE functions just need a couple of things to work as expected.  And I don't think you should have to use LASTDATE to get them to return the desired results.

First, the BALANCE functions expect snapshot data, not transaction data.  I can't really tell what kind of data you're working with, but since you have multiple records on a day, seems like that might be part of the confusion. 

Second, and more importantly, OPENINGBALANCE needs a snapshot for the last date of the previous month / quarter / year you are calculating.  So in your case, OPENINGBALANCEMONTH for 1/1/2012 is looking for a snapshot on 12/31/2011.  In your screenshots, looks like you might be missing that previous month-end-close snapshot for 12/31/2011.  Please advise if that's true.  Also, in addition to a snapshot in your [Table1] data, you must have those previous period close dates in your date dimension for the snapshot to relate to and for the function to reference.

Let me know if that makes sense.  You should be able to add that snapshot for 12/31/2011 to your Table1.  And add a DimDate record for 12/31/2011 (to be safe, I suggest adding the entire 2011 year to DimDate).  With that, I believe you should be set.  Please let me know if that produces the results you're expecting.

Cheers.

---

Brent Greenwood, MS, MCITP, CBIP
// Please mark correct answers and helpful posts //
http://brentgreenwood.blogspot.com

Answer 3

Hi Brent

Thanks for your Reply

I understand what you are saying with respect to snapshot vs transaction data, but That would make no sense with respect to this function, It would simply require a lookup into a table of balances. - If you Look at all the Texts out there on Powerpivot they all show an implemention of this function in a similiar transctional problem space. (see for eaxmple TENNICK page 285)

You are correct with respect to needing a complete Time dimension (which I have) however look in the first frame at the expansion of the pivot table . If it can tell that the Opening balance for 2/1/2012 is 12 - why cannot it tell that the opening Balance for 02-Feb is 12 also?

Answer 4

Dave -

I don't follow your comment about snapshots not making sense here.  Perhaps it's just semantics.  Just so it's clear, I'm talking about a snapshot (or more formally periodic snapshot), a dimensional modeling term for a specific type of fact that is equivalent to a balance (e.g. bank account balance as of the snapshot date).  Balances are typically close of business snapshots for a day / month / etc.  They are not the individual transactions that happened throughout the day.  They are the net effect of all the account's transactions at a given snapshot in time.  And just to be clear, the opening balance for a given day equals the closing balance from the previous day.  Hope that helps make some sense here.

Regarding texts, I don't have Tennick's book anymore, but I dug back into Russo and Ferrari's book, and they go into quite a bit of detail about using snapshots / balances with the BALANCE functions.  Not transaction data.  I'd also suggest taking a look at this write up on Time Intelligence functions by Jeffrey Wang from the AS Team.  Actually the most clear explanation of the Opening Balance functions I've seen.  From Jeffrey's explanation, the equivalent of OPENINGBALANCEMONTH: 
              Calculate(<expression>, PreviousDay(StartOfMonth(<dates>)), filter)

I was able to get my prototype working as expected at all levels (day / month / quarter / year).  Not sure why yours is not rolling up as expected.  Can you check that you've marked your "FullDateRange" as a date table?  Let me know if that helps.

---

Brent Greenwood, MS, MCITP, CBIP
// Please mark correct answers and helpful posts //
http://brentgreenwood.blogspot.com

Answer 5

Wow

OK the Solution was simply to make that the "FullDateRange" was marked as as a date table as you suggested. I have never had to do this dis the trick.

Thanks for your Help Brent I appricate it.

Dave