Forum Discussion
Minimum value of Running Total
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
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] )
5 Replies
- AlexisOlson
Super User
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] )Perfect! Thanks so much. I knew I was at the wrong granularity, just couldn't quite get it tow work. Appreciate the help!
- AlexisOlson
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] )
