cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
rudivs Frequent Visitor
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:empty_opening_balances.PNG

 

 

 

 

 

 

 

 

 

 

 

 

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
rudivs Frequent Visitor
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.

1 REPLY 1
rudivs Frequent Visitor
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.

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

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

Top Ideas
Users Online
Currently online: 263 members 2,957 guests
Please welcome our newest community members: