topic Minimum value of Running Total in DAX Commands and Tips

Minimum value of Running Total

zoberender — Fri, 29 Apr 2022 18:48:04 GMT

Hi all -

I have searched all over and can't find a solution. I'm attaching sample data and a sample PBIX file.

I have a dataset with dates, times, accounts and amounts. Each account could have multiple transactions during the day. The times are essentially categorical in that they represent a period of time (all transactions between say 7:00 and 8:00). The goal is to compute a running total each day and then find the minimum of that total for each day and be able to graph that value. I can get it working when a single account or no account is selected in the slicer, but if more than one account is chosen, I do not get the correct minimum value.

My measures:

Total Amount = SUM(Table1[Amount]) Running Account Balance (all accounts) = CALCULATE([Total Amount], FILTER( ALLEXCEPT('Table1','Table1'[Date]), 'Table1'[Time] <= MAX('Table1'[Time]) ) ) Running Account Balance = CALCULATE([Total Amount], FILTER( ALLEXCEPT(Table1,'Table1'[Account],'Table1'[Date]), 'Table1'[Time] <= MAX('Table1'[Time]) ) ) Min Running Bal = var MinRunningBalNoAccount = minx('Table1',[Running Account Balance (all accounts)]) var MinRunningBalAccount = minx('Table1',[Running Account Balance]) Return IF(ISFILTERED('Table1'[Account]), MinRunningBalAccount, MinRunningBalNoAccount)

When I select a single account or not account, I get the correct Min Running Bal. But if I select multiple accounts I don't. What I really want is for DAX to sum the running total for all selected accounts and THEN figure out the minimum.

I hope it is clear what I am talking about, but I'm happy to provide more details if needed.

I hope these links to the files work!
Excel File
PBIX File

Re: Minimum value of Running Total

AlexisOlson — Fri, 29 Apr 2022 21:46:59 GMT

You need to take the minimum at the appropriate granularity.

Try this:

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

Re: Minimum value of Running Total

zoberender — Mon, 02 May 2022 12:35:44 GMT

Perfect! Thanks so much. I knew I was at the wrong granularity, just couldn't quite get it tow work. Appreciate the help!

Re: Minimum value of Running Total

Anonymous — Wed, 22 Jun 2022 23:37:11 GMT

This is perfect!! Thank you, I've been looking for this exact solution for a couple of weeks.

Now, how would I pull the corresponding date of when the minimum running balance occurs?

Re: Minimum value of Running Total

AlexisOlson — Thu, 23 Jun 2022 14:32:00 GMT

You could filter the summary table for the minimum balance row(s) and take the date column from the filtered table.

Min Running Balance Date = VAR _Summary_ = ADDCOLUMNS ( SUMMARIZE ( Table1, Table1[Date], Table1[Time] ), "@RunBal", [Running Account Balance] ) VAR _MinBal = MINX ( _Summary_, [@RunBal] ) RETURN MINX ( FILTER ( _Summary_, [@RunBal] = _MinBal ), Table1[Date] )

Re: Minimum value of Running Total

Anonymous — Thu, 23 Jun 2022 15:38:40 GMT

Absolutely genius...Thank you!