cancel
Showing results for
Did you mean:
Highlighted
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

Accepted Solutions
Frequent Visitor

## Re: DAX formula not returning a value for every day

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

## Re: DAX formula not returning a value for every day

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

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

#### Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

#### PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 263 members 2,957 guests
Recent signins: