none
Trying to create a measure in PowerPivot for Excel RRS feed

  • Question

  • I'm having a heck of a time trying to get my head wrapped around how to make a DAX measure here, and am looking for a little help.  This is a pretty new piece to me.

    Ultimately what I'm trying to do is come up with a way to use an OLAP formula in Excel to pull back an opening balance of an Account/Dept combination given a specific date.  What I have so far is the following:

    =CUBEVALUE("PowerPivot Data",
    CUBEMEMBER("PowerPivot Data","[Query].[GeneralLedgerAccountNumber].&["&VLOOKUP(A9,tblAccounts,3,FALSE)&"]"),
    CUBEMEMBER("PowerPivot Data","[Measures].[Sum of Amount]"))

    The problem I have is that this returns the sum of all of the transactions for the account (as you'd expect.)  I need to be able to modify this formula to only show the sum up to the date shown in cell A4 (not a slicer).

    The relevant part of my data looks like this:

    • GeneralLedgerAccountNumber
    • RecordDate
    • Amount
    • Month <--derived from the DAX: =FORMAT(Query[RecordDate],"yyyy-mm")

    What I can't figure out is how to actually create a measure that works to generate the OpeningBalanceMonth measure.  I've tried so many variations, but I'm obviously lacking the syntax.  And every resource I've found seems to assume that I know what to put in the <expression>,<dates>,<filter> fields.

    Any help would be much appreciated!

    Tuesday, September 28, 2010 11:25 PM

Answers

  • I believe I have figured out what is going on in your PivotTable you have setup.   You need to swap out the Month column you are referencing in your columns from the Actuals_GL table (facts) to your tblDateMatrix (dimension).  The formula is leveraging the tblDateMatrix and you are not referencing it in the PivotTable.  Once you swap this out you will see the correct values being displayed for your OB-IS measure you have setup.


    Dan English's BI Blog
    Thursday, October 7, 2010 8:12 AM

All replies

  • Okay, I've slogged through this a bit more, and I'm trying to create my measure.  After working out that the table/query name is required (despite the expression builder offering autocomplete on the field names without that,) here's what I have so far:

    =CALCULATE(sum(Query[Amount]),
    DATESBETWEEN(Query[RecordDate],
    STARTOFYEAR(Query[RecordDate]),
    LASTDATE(PREVIOUSMONTH(Query[RecordDate]))))

    This validates as a working DAX measure, but it only returns a value for the very first month in my pivot table.  (I'm trying to pull the Sum of the Amount column for all records from the first of the year to the end of the previous month....

    (EDIT:  I'm just trying to get this working in a PivotTable first so that I know the measure is valid before trying to pull it into OLAP formulas.)

    Wednesday, September 29, 2010 5:33 PM
  • If you are trying to create an OpeningBalanceMonth measure why not use this type of a setup:

    =OpeningBalanceMonth(SUM(Query[Amount]),Query[RecordDate])

    This function will return a value from the end of the previous month.  There is also a ClosingBalanceMonth function which pulls the balance for the last day of the month.


    Dan English's BI Blog
    Wednesday, September 29, 2010 8:16 PM
  • Thanks for the reply, Dan.

    The reason I avoided it is because I didn't have any luck with it, and didn't know if it's supposed to give me the opening balance for assets/liability accounts (carryforward from day 1) or P&L accounts (carryforward from Jan 1).

    Regardless, I plunked the formula you gave into a measure and it returns nothing for me if I don't pick a specific day.  Maybe this makes a difference...

    My PivotTable is actually set up with Accounts as Rows, Month as columns, and Sum of Amount in the data area.  I'm trying to filter a month at a time.  Am I off base in expecting that my pivot table should be able to show me 2010-01's Sum of Amount as well as the opening balance, in the same table as 2010-02's?  (02's shoudl equal 01's opening + 01's transactions of course.)

    Wednesday, September 29, 2010 8:57 PM
  • It does work as long as data exists for the last day of the previous month.  If there are no values then you will get a blank result.  Do you have values for every day including the last day of the months?  If so then as long as you have the months included you should be seeing results.  If not then you are going to have to come up with a different solution that grabs the LASTNONBLANK value for the LASTDATE.
    Dan English's BI Blog
    Wednesday, September 29, 2010 9:34 PM
  • Hmmm, interesting...

    No, can't guarantee values for each day.  It's a G/L listing that records the sales for the day by category.  So if no one buys a beer that day, there's no value.  (It's food and beverage.)

    I'll admit to being a bit confused on this.  If I filter the PivotTable using the RecordDate slicer, the TOTAL measue shows a value.  But I can't figure out what the heck it's referring to, as it doesn't follow any pattern.  Maybe this is due to blank dates, I'm not sure.  Because of this, I flipped to using the following measure:

    =CALCULATE(SUM(Query[Amount]),
    DATESBETWEEN(Query[RecordDate],
    STARTOFYEAR(query[RecordDate]),
    PREVIOUSDAY(STARTOFMONTH(Query[RecordDate]))))

    Now this appears to work, but only if I select the span of dates using the RecordDate slicer to cover the year.  If I try using my Month slicer instead, it doesn't calculate anything. I've tried monkeying with that, but gave up.

    Regardless, it seems that this does do what I need, and I can use this as an OLAP measure now.  The PivotTable was a means to an end, so I'm not stuck on making that work.

    I am curious why this only shows in the total column though, and not next to the individual months?

    Wednesday, September 29, 2010 10:51 PM
  • Ugh... may have spoken to soon.

    I've got it into a nice OLAP formula that looks like this:

    =IFERROR(CUBEVALUE("PowerPivot Data",
    CUBEMEMBER("PowerPivot Data","[Query].[RecordDate].&["&TEXT(EOMONTH(Date,-1),"yyyy-mm-ddThh:mm:ss")&"]"),
    CUBEMEMBER("PowerPivot Data","[Query].[GeneralLedgerAccountNumber].&["&VLOOKUP($A34,tblAccounts,3,FALSE)&"]"),
    CUBEMEMBER("PowerPivot Data","[Measures].[OB-IS]","Total OB-IS")),0)

    (The record date is returning the 1st of the month)

    Problem is that it works for my revenues which typically have entries every day.  My costs, on the other hand, are returning blanks as no entries have been posted to the accounts this month yet.  Obviously this goes back to my measure.  There must be a way to get an answer out of it even if it has no dates in the current month to work with...  I suppose I could try to convert the query to return the end of period balance, but something tells me that I could still run into issus if there were no transactions in the previous month (even though there may have been some in the month prior to that.)

    Should I be trying to pull my dates from a different table all together? A table that lists every day of every year?  That seems like it shouldn't be necessary to me...

    Wednesday, September 29, 2010 11:31 PM
  • Ah, should have realized that.  You don't have date dimension table to reference that has the full set of dates.  When working with the Time Intelligence functions you will definitely want to have a table that is related to your facts that has the complete set of dates.  Not only just for the current year, but previous and most likely more.

    Take a look at Kasper's Golden Rules - http://www.powerpivotblog.nl/powerpivot-time-intelligent-functions-golden-rules

    The formula you created will work at the month level, just be aware that it will return the values from the beginning of the year through the end of the previous month.  So that calculation will basically give you a accumlated total through the end of the previous month.

    If you want the total sum for the previous month you would do the following and this is now using a separate date table reference:

    =CALCULATE(SUM(Query[Amount]),PREVIOUSMONTH(Calendar[Date]))

    If you need any help building out the Calendar table here are a couple of postings with some examples from Vidas and Kasper:

    http://powerpivot-info.com/post/208-q-how-can-i-create-calendar-datetime-table-for-powerpivot-if-i-do-not-have-any-source-for-that

    http://www.powerpivotblog.nl/calling-a-stored-procedure-in-powerpivot-to-populate-your-date-table


    Dan English's BI Blog
    Thursday, September 30, 2010 10:59 AM
  • Hi Dan,

    Okay, so I've created a linked Excel table with a column of historical dates going back to 2003 (as far as my data goes right now.)  I've also done some overdue cleanup on my tables, and linked the renamed table to the new date table.  I'm still having issues though.  My query now looks like this:

    =CALCULATE(SUM(Actuals_GL[Amount]),
    DATESBETWEEN(Actuals_GL[RecordDate],
    STARTOFYEAR(tblDateMatrix[Dates]),
    PREVIOUSDAY(STARTOFMONTH(tblDateMatrix[Dates]))))

    So I do see some improvement in that when I put this in a PivotTable the measure actually shows up in the columns, not just the totals.  The problem is that it is giving me the exact same number as the sum of the month's transactions.  And if there is no transactions in the month, no value.

    The following gives the same results :

    =CALCULATE(SUM(Actuals_GL[Amount]),
    DATESBETWEEN(tblDateMatrix[Dates],
    STARTOFYEAR(tblDateMatrix[Dates]),
    PREVIOUSDAY(STARTOFMONTH(tblDateMatrix[Dates]))))

    This gives blank values all across the board:

    =CALCULATE(SUM(Actuals_GL[Amount]),
    DATESBETWEEN(tblDateMatrix[Dates],
    STARTOFYEAR(Actuals_GL[RecordDate]),
    PREVIOUSDAY(STARTOFMONTH(Actuals_GL[RecordDate]))))

    I'm stumped here...

    Thursday, September 30, 2010 9:47 PM
  • The second DAX formula should be the one you are looking for (the one referencing tblDateMatrix[Dates] for everything) if you want a running total from the first of the year through the end of the previous month.  For the first month of the year you won't have any value and then it will start to be a running total from there on out.

    You should have the Actuals_GL table setup with a relationship using the tblDateMatrix as the lookup table using the date column as the linkage.

    It might help if you provide a smalll sample of what you are actually seeing and what you are expecting to get a better understanding of how to resolve the exact issue you are encountering.


    Dan English's BI Blog
    Friday, October 1, 2010 4:17 PM
  • Hi Dan,

    Sorry, I didn't realize that you had replied.  For some reason I never got an email notification...

    Okay, so let's see what I've got here...

    I have a Pivot Table set up like this (sorry, my HTML sucks, so this is pretty ugly):

      2010-08   2010-09   Total Sum of Amount Total OB-IS
    Row Labels Sum of Amount OB-IS Sum of Amount OB-IS    
    61510 -7041.85 -7041.85 -4175.64 -4175.64 -11217.49 -11217.49
    63050  -11725.00  -11725.00     -11725.00 -11725.00
    86020            

    The measure I created, using the second DAX formula above is called OB-IS.  And yes, I'm looking for that running balance starting from 0.00 at Jan 1, accumulating up to the end of the previous month.  The other columns in the Pivot Table are simplay a "Sum of Amount" column.  So basically, the table is broken down with Accounts on the left, Month across the top, Amount and OB-IS in the middle.

    The challenges I'm having with this are:

    • OB-IS, based on the DAX measure, should be returning the value from 1/1/2010 through the end of the previous month.  In the case of 61510, August's OB-IS values should be -18,211.01.  September's opening (OB-IS) should therefore be -25,252.86 (-18,211.01+-7,041.85)
    • Where there are no values posted for an account at all, the OB-IS is coming up blank.  In the case of 63050, August's OB-IS value should be -10,916.03.  September's OB-IS should therefore be -22.641.30 (-10,916.03+-11,725.00)
    • Similar to what's happening with 63050, 86020 has no values for either month.  But in Feb & Apr there were transactions, so the OB-IS for both months should read 415.47.
    • I don't have anything in this table where there are transaction in Sep, but none in Aug.  If I did though, based on what I'm seeing, the Aug columns would be blank, and the Sep one showing the values for the current month only.

    For the record, the only reason that the Sum of OB-IS is in the table is that it's automatically placed there.  I couldn't care less what that says at this point.  I'm far more interested that the monthly openings are showing correct values.

    To me it almost looks like the filters on dates are just being ignored.  I just can't figure out why.  There is a relationship from Actuals_GL.RecordDate to tblDateMatrix.Date where the latter contains a full list of dates as far back as 2003.

    I hope this helps a bit.  If you need any more info (or would like a copy of the workbook emailed) just let me know.  I appreciate your help with this!


    Ken Puls, CMA, MS MVP(Excel) I hate it when my computer does what I tell it to and not what I want it to...
    Wednesday, October 6, 2010 9:16 PM
  • I am reviewing this and I will try to setup a sample file to try and simulate what you have going on.  If you are able to you can feel free to send me the workbook to see what you have going on - denglishbi@gmail.com
    Dan English's BI Blog
    Thursday, October 7, 2010 12:54 AM
  • Just got the email notification (seems slow from this forum.)  There should be an email in your inbox.  :)
    Ken Puls, CMA, MS MVP(Excel) I hate it when my computer does what I tell it to and not what I want it to...
    Thursday, October 7, 2010 2:20 AM
  • I believe I have figured out what is going on in your PivotTable you have setup.   You need to swap out the Month column you are referencing in your columns from the Actuals_GL table (facts) to your tblDateMatrix (dimension).  The formula is leveraging the tblDateMatrix and you are not referencing it in the PivotTable.  Once you swap this out you will see the correct values being displayed for your OB-IS measure you have setup.


    Dan English's BI Blog
    Thursday, October 7, 2010 8:12 AM
  • Thanks Dan, that seems to be working.  I can also expand on this to get my opening asset balances by modifying the DAX formula to read:

    CALCULATE(SUM(Actuals_GL[Amount]), FIRSTDATE(all(tblDateMatrix[Dates])),PREVIOUSDAY(STARTOFMONTH(tblDateMatrix[Dates]))))

    So, is this the rule with the filters then?  The Pivot Table columns and Filter columns need to be in sync to display properly?  With Kasper's golden rules, I assume that this means that the date column headers on the Pivot Table should also always come from the Calendar table?  (tblDateMatrix in my case.) 


    Ken Puls, CMA, MS MVP(Excel) I hate it when my computer does what I tell it to and not what I want it to...
    Thursday, October 7, 2010 3:51 PM