Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |