DAX issue - opening and closing balance for month, Error in OPENINGBALANCEMONTH, CLOSINGBALANCEMONTH

[Sarvan]

Hi, dear colleguas!

I have some troubls with calculations Opening and Closing balance for month by using functions OPENINGBALANCEMONTH, CLOSINGBALANCEMONTH.

May be you already exeperinced this issue, i kindly ask your suggestions as practical solution.

So, my test sales data is:

TransDate  CustCode Amount
05.05.2015  68992      -2
10.05.2015  68992      -2
05.06.2015  68992      -3
10.06.2015  68992      -3
05.07.2015  68992      -2
05.08.2015  68992     -5
10.08.2015  68992      1
05.09.2015  68992      6
10.09.2015  68992      9
05.10.2015  68992     -4
10.10.2015  68992     -5
05.11.2015  68992      5
05.12.2015  68992      6
10.12.2015  68992     -2

Firstly calculated Cumulative balance with formula:

BalanceAll = 
CALCULATE (
    SUM ( TestData[Amount]);
    FILTER ( ALL (TestData[TransDate] );TestData[TransDate]  <= MAX (TestData[TransDate] ) )
)

Then used this measure in OPENINGBALANCEMONTH as:

OpenBalanceTest = OPENINGBALANCEMONTH([BalanceAll];TestData[TransDate])

The same logic used for CLOSINGBALANCEMONTH:

ClosingBalanceTest = CLOSINGBALANCEMONTH([BalanceAll];TestData[TransDate])

As you can see in this result table, Opening and CLosing balance calculates incorrectly, only for some months correctly.

For example,

- Closing month balance for 05.07.2015 (Jul 2015) not showing

- Opening month balance-mot for 05.08.2015 (Aug 2015) not showing

- Closing month balance for 05.11.2015 (Nov 2015) not showing

- Opening moth balance for 05.12.2015 (Dec 2015) not shwing.

Opening & Closing balances

My be i missed some important point, but, i'm trying to get Month Opening and Closing balance for TransDate - even if there is not transactions in month, we have to calculate Opening and Closing balance- if there are transactions in previous months.

I hope could give correct explanation.

Thank you!

[v-qiuyu-msft]

Hi @Sarvan,

Based on your sample data, the results from three measures works fine, see:

Best Regards,
Qiuyun Yu

[Sarvan]

Hi, @v-qiuyu-msft!
Thank you for you reply, really it made me happy, when for a long time nobody answered!

I have prepared this test data for introducing my issue.

Yes, you are right, there was Calendar table - related with TestData.TransDate , i acreated it for using YTD analyse, which result withut it incorrect was. But, when I delete relation with Calendar table, then measure calculates correctly, other way round incorrect.

I decided to share with original data in my test pbi table, pelase see it(onedrive link, because i have no option "attach file" ).

Although there are transactons in previous months, sometimes opening and closing balance not calculating.

https://1drv.ms/u/s!ApCWIMno0N6ShkgPoGJlWeXoLHEz

https://1drv.ms/u/s!ApCWIMno0N6ShkgPoGJlWeXoLHEz

Thanks a lot!

Regards,

Sarvan.