Running account balance - count less than certain amount

[zoberender]

Hi all - I've searched all over the place and can't seem to figure out the solution to my problem.  I'm hoping someone can help!

I have a table with Dates, Times, Bank Accounts and Transaction Amounts.  There are multiple transactions per account/day/time.  We monitor these "balances" throughout the day, so I have a measure to calculate the running balance.  

Total Amount = SUM('Final Values'[Amount])

Running Account Balance = 
CALCULATE([Total Amount], 
    FILTER(
        ALLEXCEPT('Final Values','Final Values'[Account Name & #],'Final Values'[Date]),
        'Final Values'[Time] <= MAX('Final Values'[Time])
    )
)

That Running Account Balance measure allows us to see how the account balance changes throughout the course of a day as the user selects different dates/accounts.  In this view, there is also a slicer on Account Name & #.    

We are also interested in what the minimum daily account value is for each day.  I have a measure that calculates that daily minimum balance.  

Min Running Balance = VAR _Summary_ =
    ADDCOLUMNS(
        SUMMARIZE ( 'Final Values', 'Final Values'[Date], 'Final Values'[Time] ),
        "@RunBal", [Running Account Balance]
    )
RETURN
    MINX ( _Summary_, [@RunBal] )

This measure allows the user to select a month and see a graph of the minimum running balance by day.  

 

All I want to do is add a card that shows the Count of how many times the Minimum balance was <-1billion.  In the example graphs above, that would be 1 for June and 2 for March.  

It feels like this should be super simple, but I just cannot figure out the correct DAX code to make it work.  If anyone can help me out I would greatly appreciate it.  

[daXtreme · Accepted solution]

Hi @zoberender 

This is my second reply. Please see the first one as well.

Here's probably the measure you want:

[# Min Bal < -1bln] =
// change the threshold to the correct number
var MinBalanceThreshold = -1000
var Result =
    CALCULATE(
        COUNTROWS(
            FILTER(
                VALUES( 'Final Values'[Date] ),
                [Min Running Balance] < MinBalanceThreshold
            ),
        ),
        ALLSELECTED( 'Final Values' )
    )
return
    Result

However, I must warn you and this is a huuuuuuge warning.

Your model seems to be a one-table model since I can see you're storing dates and times in the same table as the other entities (like, say, account names). This is INCORRECT by a long shot and will very soon start producting wrong numbers without you even realizing it. And yes, I know what I'm saying. I've been working in this field for much too long not to know the pitfalls.

Please, for your own sanity and safety, change the model to a correct one, meaning you have to create a proper star schema. Here are the articles you should read (everyone should if they want to create predictable and correct models):

1. The importance of star schemas in Power BI - SQLBI (bing.com)

2. Understand star schema and the importance for Power BI … (bing.com)

There are also videos on YT by Marco Russo and Alberto Ferrari that explain in detal why one-table models are BAD. In fact, they're so bad that it's very often impossible to spot the wrong figures they produce.

So... in a word, you've been warned and it's up to you what you're gonna do with this knowledge. Use it to your advantage 😉

[zoberender]

Ah shoot.  You noticed.  This wasn't something that was supposed to be a long term dashboard, so I had thrown it together quickly.  That has now changed and I'm in the process of re-doing the whole thing (should have done it correctly to start!).  It'll be a proper star schema when I'm done with it :-).  

I'll review the solutions you provided today and accept as solutions if they work.  Thanks for the help!!

[daXtreme]

Hi @zoberender 

A piece of good advice: Whenever you embark upon a PBI project, start with the right basics in place because even if you later throw it away, you might stumble across issues in a bad model that will be totally obscure and you won't be able to get the the back of them despite spending countless hours trying to understand what's going on (you won't be able to, trust me). They'll be completely inexplicable and you'll waste a lot of time trying to re-structure your DAX code but it'll be to no avail because the problem will be the model itself, not your DAX. I've seen this happening too many times with one-table models.

[zoberender]

I appreciate the input daXtreme!  You are right that is best practice and I don't disagree at all.  

I updated my Min Running Balance measure to your suggestion and it does seem to be more performant.  I then tried the # Min Bal < -1bln measure and receive the below error.  

Any additional thoughts?

Thanks again for all the help and input!

[daXtreme]

Always check your syntax. COUNTROWS takes a table as the only argument. In my code the argument is FILTER which always returns a table.

[zoberender]

Thanks @daXtreme - I found the issue.  There was an extra , in the code after the ) that closed the filter function.  The measure works beautifully!  Now off I go to convert this into a proper star schema 🙂

[zoberender]

Hi @daXtreme - Did you happen to have any thoughts on the error I received on the Countrows function?  Thanks!

[daXtreme]

Hi @zoberender 

First, you could make the second measure probably more performant by replacing it with:

[Min Running Balance] =
MINX(
    SUMMARIZE(
        'Final Values',
        'Final Values'[Date],
        'Final Values'[Time]
    ),
    [Running Account Balance]
)

The above most likely does not need to materialize the measure values all in one go but your formula has to due to ADDCOLUMNS. As usual, when in doubt - test with Performance Analyzer and DAX Studio.

I'll take a look at the other bit as well. Bear with me, pls.