cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## DAX formula not returning a value for every day

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?

1 ACCEPTED SOLUTION
Frequent Visitor

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.

Frequent Visitor

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors