Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Sarvan
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
v-qiuyu-msft
Community Support
Community Support

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.

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.