cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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

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 balancesOpening & 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!

 

 

 

2 REPLIES 2
Highlighted
Community Support
Community Support

Re: DAX issue - opening and closing balance for month, Error in OPENINGBALANCEMONTH, CLOSINGBALANCEM

Hi @Sarvan,

 

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

 

q3.PNG

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Frequent Visitor

Re: DAX issue - opening and closing balance for month, Error in OPENINGBALANCEMONTH, CLOSINGBALANCEM

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.

 

Screen Shot 2016-08-15 at 13.38.55.png

 

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.

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors