Skip to main content
cancel
Showing results for 
Search instead 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

Reply
rudivs
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
rudivs
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.

View solution in original post

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

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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