Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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!
Hi @Sarvan,
Based on your sample data, the results from three measures works fine, see:
Best Regards,
Qiuyun Yu
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.
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
60 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |