Opening balance calculation

[Anonymous]

Hi,

I am trying to calculate opening balance for each month from my available data. I have also added a slicer for month to see the opening balance for selected month. I have used following formula (Referred from a solution in community messages - https://community.powerbi.com/t5/Desktop/DAX-Closing-and-Opening-Balances/m-p/232590#M103612😞

Opening Balance =
CALCULATE(
    SUM(table1[Balance]),
    FILTER(
        ALL(table1),
        SUMX(
            FILTER( table1,
                EARLIER(table1[Date]) = LASTDATE(PREVIOUSMONTH(table1[Date]))),
         (table1[Balance])
         ))

Everything is working fine except that if i crossfilter (based on interactions), opening balance remains constant. Only slicer seems to be working fine. I am assuming that this is because of ALL function in the formula. However if i remove ALL, the column goes blank for opening balance. 

Can someone help me with the situation.

Thanks in Advance

[v-danhe-msft]

Hi @Anonymous,

Could you have tried with a measure instead of a calculated column? Or you could use the OPENINGBALANCEMONTH function to have a try. If the problem still persists, could you please offer a sample file to have a test and post your desired result if possible?

Regards,

Daniel He

[Anonymous]

https://drive.google.com/open?id=1i3MMEhFoJEGX6KMouRk9nMgNJWaFGwyX

Hi @v-danhe-msft,

Attached is the link to the test file. I need my opening balances to be divided betweent the sources, however i get the total across all data sources. How to make that change?

Thanks