Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!To celebrate FabCon Vienna, we are offering 50% off select exams. Ends October 3rd. Request your discount now.
I am calculating a fund balance (essentially account balance) as a measure:
Fund Opening Balance = CALCULATE( SUM('Fund Balances'[Opening Balance]), FILTER('Fund Balances','Fund Balances'[Year] = YEAR([First Day of Previous Month])), FILTER('Fund Balances','Fund Balances'[Month] = MONTH([First Day of Previous Month])) ) + CALCULATE(SUM('Fund Balances'[Movement]), FILTER('Fund Balances','Fund Balances'[Year] = YEAR([First Day of Previous Month])), FILTER('Fund Balances','Fund Balances'[Month] = MONTH([First Day of Previous Month])) )
where
First Day of Previous Month = STARTOFMONTH(PREVIOUSMONTH(Dates[Date]))
Basically, the opening balance of this month is equal to the opening balance of the previous month + the movement in that month.
This calculation works, but does not return a value for every date, as can be seen in this table:
This results in an incorrect fund balance for many days, since fund balance = Fund Opening Balance + Fund Movement. Any idea what's wrong?
Solved! Go to Solution.
I figured out what was causing the problem. One of the relationships with the Funds lookup table was set to bidirectional. Setting it to 'Single' sorted out the missing values.
I figured out what was causing the problem. One of the relationships with the Funds lookup table was set to bidirectional. Setting it to 'Single' sorted out the missing values.