Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Opening balance calculation

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

Need Help

2 Replies

  • v-danhe-msft's avatar
    v-danhe-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    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