none
Get account balance using an inactive relationship via the account table

    Question

  • I have a 2012 SSAS database the contains share balances (Shares are the same as Accounts, it's for a Credit Union).  The databas design is as follows:

    I'm able to get account balances by month using the following formula:

    Share Balance:=CALCULATE(sum([Balance]), LASTDATE('Date'[Date]))

    What I now want is the balances for shares (Certificate Deposits have maturity dates) that will mature in the future.  Something like the following:

    The above was created with the following formula:

    Share Balance by Maturity Date:=CALCULATE(sum([Balance]), LASTDATE('Date'[Date]), USERELATIONSHIP('Share'[Maturity Date], 'Date'[Date]))

    The results are incorrect.  One problem is that I am getting prior months with values.  For example, I find a CD that matured in February of 2013.  The current balance is zero, so it shouldn't even show up.  It was a $2,000 CD that spanned 7 months.  My formula returned a balance of $14,005.42.  The results is the sum of 7 months of month end balance.  This makes sense because DAX is finding 7 ShareBalance entries and all have a link to a Maturity Date, which is used in the UseRelationship function.   Since my Maturity Date is in the Shares table, I'm now concerned that I might not be able to do what I want.  I just want current balances group by Maturity dates.

    I have attempted a second formula that nests two different UseRelationship functions, but unfortunately it returns the same results:

    Share Balance by Maturity Date2:=CALCULATE(CALCULATE(sum([Balance]), LASTDATE('Date'[Date]), USERELATIONSHIP('ShareBalance'[Balance Date], 'Date'[Date])), USERELATIONSHIP('Share'[Maturity Date], 'Date'[Date]))

    Any help on this would be appriciated.

    Regards,

    Bill

    Wednesday, July 31, 2013 11:15 PM

Answers

  • Bill,

    thanks for the feedback.

    The two queries you wrote would suggest me that you are using the Date-Share relatioship in *all* the steps of your query, and you are never using the default one. You wrote statements that reproduce the default relationship in the innermost calculation (using the direct filter on ShareBalance[Balance Date] in both conditions), but actually you are not using the default relationship in any case. Maybe your second version is also better than any alternative. I'm just wondering whether using a different approach you could further improve performance by inverting the approach: have you tried filtering the ShareBalance table through a filter on Maturity Date column (with a FILTER in the CALCULATE) and removing the SUMX and using a simple SUM in the middle. Something like:

    Share Balance by Maturity Date :=
    CALCULATE (
        SUM ( ShareBalance[Balance] ),
        FILTER (
            VALUES ( Share[Maturity Date] ),
            CONTAINS ( VALUES ( 'Date'[Date] ), 'Date'[Date], Share[Maturity Date] )
        ),
        'Date'[Date] = EOMONTH ( TODAY (), -1 )
    )

    Not sure it will work - the last row could be changed using a last day of the selected period instead of the last month, if you need it...

    • Marked as answer by bhoenig Tuesday, September 24, 2013 11:49 PM
    Thursday, September 19, 2013 10:19 PM

All replies

  • Checking back in. Has anyone done something like this here?

    Thanks!


    Ed Price, SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Wednesday, August 21, 2013 7:23 PM
    Owner
  • I'll attempt to summarize the above request.  Our finance department wants to know what loans are maturing in the future.  If we have $20 million dollars worth of CD's maturing in December, they need to know this.  They are not concerned what the actual balances will be because the interest is immaterial.  They just want the current balance of the loan grouped by maturity date. 

    Any direction on this would be greatly appreciated.

    Regards,

    Bill

    Wednesday, August 21, 2013 10:55 PM
  • Any ideas for Bill?

    Thanks!


    Ed Price, SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Friday, September 13, 2013 7:08 PM
    Owner
  • I'd like to make a suggestion for this type of questions. Please, post a sample workbook and a screenshot of the results obtained with the current formula and the desired result.

    The time available is always small and even if this question is interesting, it would require time to create a repro and a sample formula, instead of guessing the formula on the forum and then spending a thread looking for details that were not included at the beginning.

    For this reason I prefer to answer to question that can be useful to a larger audience (like a public repro could be) and not just a solution for a specific issue (which is what I do for a living as a consultant, also remotely). But I think that this suggestion can improve the chance to get an answer by everyone, not just be me (I do not check the forum on a daily basis, unfortunately).

    Thanks,

    Marco


    Marco Russo http://ssasworkshop.com http://www.sqlbi.com http://sqlblog.com/blogs/marco_russo

    Saturday, September 14, 2013 3:49 PM
  • Hi Marco.  Thanks for your suggestion.  I agree with your point and will attempt to do that in the future.  I was originally hoping that someone would send me a link to someone’s blog that would get me going the right direction.  I now have a better understanding of what is needed to solve this problem and understand why you made your suggestions. 

    The good news, I have solved this problem last week.  I’m now just getting back to post my solution.  In order for me to solve this, I really needed to have a solid understanding of DAX first. 

    The blog that got be the closest was Chris Webb’s Blog (A Different Approach To Last-Ever Non-Empty in Dax) http://cwebbbi.wordpress.com/2013/01/15/a-different-approach-to-last-ever-non-empty-in-dax/

    Your book was also an often used reference while researching and solving this problem. 

    The solution first required a Last-Ever Non-Empty using the share balance date (active join).  Once I had the last-ever non-empty, I then join its results to the date table with the inactive Maturity Date relationship.  It sounds simple now, but there was still one performance gotcha.

    Here is my first solution that has serious performance issue once I deployed it to production.  My production table has 91 million rows spread accross 4 years.  The query never finishes and crashes after it consumes about 80 GB of RAM.  The server memory was consumed slowly over about an hour.

    Share Balance by Maturity Date (Performance Issues):=CALCULATE(SUMX('Share', 
    CALCULATE
    (
      SUM(ShareBalance[Balance])
    , CALCULATETABLE
        (
          LASTDATE(ShareBalance[Balance Date])
        , DATESBETWEEN('Date'[Date], BLANK(), LASTDATE('Date'[Date]))
        , ALL('Date')
        )
    , ALL('Date')
    )
    )
    , USERELATIONSHIP(Share[Maturity Date], 'Date'[Date] )
    )

    The good news is that my table has share balances for all accounts at month-end.  During the month it only has balances for shares that change.  Since, I only needed to get that balance of prior month-end, I was able to swap out the DATESBETWEEN filter.  I first get the prior month balance by share balance date, then group this balance by Maturity Date.  The solution below runs within 3 seconds.

    Share Balance by Maturity Date:=CALCULATE(SUMX('Share', 
    CALCULATE
    (
      SUM(ShareBalance[Balance])
    , eomonth(Today(), -1) = ShareBalance[Balance Date]
    , ALL('Date')
    )
    )
    , USERELATIONSHIP(Share[Maturity Date], 'Date'[Date] )
    )

    Regards,

    Bill Hoenig

    • Edited by bhoenig Thursday, September 19, 2013 9:29 PM
    • Marked as answer by bhoenig Thursday, September 19, 2013 9:31 PM
    • Unmarked as answer by bhoenig Tuesday, September 24, 2013 11:49 PM
    Thursday, September 19, 2013 9:28 PM
  • Bill,

    thanks for the feedback.

    The two queries you wrote would suggest me that you are using the Date-Share relatioship in *all* the steps of your query, and you are never using the default one. You wrote statements that reproduce the default relationship in the innermost calculation (using the direct filter on ShareBalance[Balance Date] in both conditions), but actually you are not using the default relationship in any case. Maybe your second version is also better than any alternative. I'm just wondering whether using a different approach you could further improve performance by inverting the approach: have you tried filtering the ShareBalance table through a filter on Maturity Date column (with a FILTER in the CALCULATE) and removing the SUMX and using a simple SUM in the middle. Something like:

    Share Balance by Maturity Date :=
    CALCULATE (
        SUM ( ShareBalance[Balance] ),
        FILTER (
            VALUES ( Share[Maturity Date] ),
            CONTAINS ( VALUES ( 'Date'[Date] ), 'Date'[Date], Share[Maturity Date] )
        ),
        'Date'[Date] = EOMONTH ( TODAY (), -1 )
    )

    Not sure it will work - the last row could be changed using a last day of the selected period instead of the last month, if you need it...

    • Marked as answer by bhoenig Tuesday, September 24, 2013 11:49 PM
    Thursday, September 19, 2013 10:19 PM
  • Hi Marco,

    Thanks for reviewing my formula.  Your suggested formula is much faster.  It runs in a spit second. 

    The frustrating part for me is that I can’t make sense of why your query works.  It all somehow hinges on the FILTER.  To make sense of it, I did a lot of trial and error by replacing FILTER with many different approaches.  I just don’t see why your filter uses the inactive relationship (you never use USERELATIONSHIP).  Also, if I remove the FILTER, the formula doesn’t even follow a default relationship to the Date table (it just returns the prior month-end balance for all dates in the Date table). 

    That said, I really do appreciate your input and will be using your query. 

    Thank you,

    Bill

    Tuesday, September 24, 2013 11:52 PM
  • Bill,

    my formula don't use the inactive relationship at all. By using the CONTAINS condition, I filter the rows in Share table applying a filter on Share[Maturity Date] column. This filter propagates to ShareBalance. I only filter the [Maturity Date] values that satisfy the condition in the CONTAINS function. The information about the relationship is in fact in the DAX code now (the CONTAINS call) instead than in the data model.

    You can write the same expression in many ways in DAX, and you can avoid using the relationship. However, relationships are usually faster. In this case, we have to use this approach because of some limitation in using USERELATIONSHIP when a circular references in relationship is involved (you have to use the inactive form to avoid that).

    Marco


    Marco Russo http://ssasworkshop.com http://www.sqlbi.com http://sqlblog.com/blogs/marco_russo

    Tuesday, September 24, 2013 11:59 PM
  • Thanks again Marco,

    It makes much more sense now.  I removed my inactive relationship between Share-Date (since it's not being used).

    Bill

    Wednesday, September 25, 2013 12:23 AM