Mynda,
The solution you gave me for the running totals has been working great, but I'd like to create a version where I can add more detail. I've attached the same example spreadsheet as before, where the top table on the "MoBal" tab works as it should, as evidenced by the "Check Column" I added on the side. When I create the same table below, and add a column for the names, the running balance no longer works. Can you help?
Thanks,
Luciana
Hi Luciana,
Add ALL(Main[Depositor/Payee]) to CALCULATE context, or use the following simplified measure:
RunBal3:=IF (
COUNTROWS(Main)>0,CALCULATE (
SUM([Credits])-SUM([Debits])+SUM(AcctList[Beg Bal]),
FILTER (
ALL ( 'Calendar'[Date] ),
'Calendar'[Date]<= MAX ( 'Calendar'[Date])&&MIN ( 'Calendar'[Date] ) <= CALCULATE ( MAX ( Main[Posted Date] ), ALL ( Main) )
),ALL(Main[Depositor/Payee])),BLANK())
You will notice that there will be duplicate balances where there are duplicate dates in the dates column, mostly because there can be only 1 balance for that day, the calculations are made by date.
Thank you Catalin,
I'm actually trying to get it to calculate a balance after each individual item, even if there are several items on the same day. I've tried adding an Index column at the beginning but that doesn't seem to be working either. In other words, I'm trying to get the same results as what is in the "Check Column."
Thanks,
Luciana
Hi Luciana,
When you add the index column in the Main query, make sure that the table is sorted properly by Posted Date and that detail column before adding the index, otherwise it will be messed up.
Then, use this measure to calculate the running balance:
RunBal3:=CALCULATE (
SUM(Main[Credits])-SUM(Main[Debits])+SUM(AcctList[Beg Bal]),
Filter(ALL(Main),Main[Index]<=MAX(Main[Index])&&Main[Stmt Acct #]=MAX(Main[Stmt Acct #])))
Catalin,
The measure calculates the balances correctly but it has significantly slowed down the spreadsheet. It is about 24000 lines, but I didn't notice the slowdown on the prior version of the measure (where it calculated by date). Is this normal?
Thanks,
Luciana
How slow? 1 minute, 1 hour?
I let it run for 15 minutes before I stopped it.
Hi Luciana,
If you use variables, is it faster?
RunBal3:=Var Acct=MAX(Main[Stmt Acct #])
Var Indx=MAX(Main[Index])
Return
CALCULATE (
SUM(Main[Credits])-SUM(Main[Debits])+SUM(AcctList[Beg Bal]),
Filter(ALL(Main),Main[Index]<=Indx&&Main[Stmt Acct #]=Acct))
I tried that and it took about 30 minutes to complete the calculations. Then if I changed any of the filters, it would take another 30 minutes to recalculate. Have you ever encountered this before?
Hi Luciana,
Don't use DAX that much for myself, did not met this problem before.
Will look into that to see if I can come up with a more efficient solution.
