I got General Ledger, from General Ledger I need to create Balance Sheet.
In GL table their is Activity column that records each GL account change.
1. Problem is to show cumulative data for all account in Balance Sheet to a certain date.
2. Problem, GL table doen's have Retained Earning account, as we know RE ( Beg Balance + Net Income - Div = End Balance)
GL ID | Activity | Trans Date | GL Type
1 | 100 | 1/1/2015 | Assets
2 | 2000 | 1/1/2015 | Liability
4 | 1300 | 1/1/2015 | Income
1 | -500 | 1/2/2015 | Assets
2 | -700 | 1/2/2015 | Liability
4 | 200 | 1/2/2015 | Income
5 | 100 | 1/2/2015 | Expenses
1 | -500 | 1/2/2016 | Assets
2 | -700 | 1/2/2016 | Liability
4 | 1200 | 1/2/2016 | Income
5 | 100 | 1/2/2016 | Expesnes
Balance Sheet Structure
Assets = Libility + Equity (Beg Balance (Zero) + Net Income - Div = End Balance)
This would be such a simple thing for Microsoft to publish and help out the PowerBI Community. I don't understand why Microsoft hasn't made the way the calculations are done for the Quickbooks Online content pack made avaialble publicly. They aren't selling the content pack, so they wouldn't be losing any money. The poster is correct in that there is much more you can do by using Power BI Desktop to connect to the Quickbooks Entities and do your own calculations. The big problem with the Content Packs is you can't see what is behind their calculations, and you also can't bring in an additional data sets (a great example is financial targets for your organization) to extend the content pack reports.
Hey - please reach out on linked in if you'd like to talk / share notes on this point:
Has anyone figured out a solution to this? I'm also trying to access/see the DAX used for the measures on the QBO content pack. I want to be able to use the same measures on PBI Desktop.
If you need to calculate Retained Earning you will need to add this to your account table and make some kind of many to many relationship between your account table and GL transaction table to get what you want.
Actually, I'm not very clear about what you want. But if you connect to QuickBooks Online data from Power BI, actually you can get a Power BI dashboard and Power BI reports that provide insights about your business cash flow, profitability, customers, and more. See:
If you have any question, please feel free to ask.
For Quickbooksonline we are getting data model only online, desktop version get's raw tables.
I need to General Ledger to Balance Sheet Financial Statement from different accounting system not quickbooks or QB online.
If someone can reveal secret how Quickbooksonline model measures was created would be great.
Did you figure this out? I use Quickbooks Online, and I've had a lot of trouble getting things to work in PowerBI.
I've tried to do essentially the same thing that you're describing, ideally in PowerBI desktop, because it's a much richer development environment than PowerBI Service.
If you or anyone has figured out how to properly map/manage General Ledger table, I'd be very interested to hear details.
Yes, pulling a retained earnings balance is something I've not been able to do. But I couldn't even pulling a complete list of customer invoices.
To get the Retianed Earnings balance, you have to include ALL of the P&L Accounts, because they are posted to Retained Earnings at year end.
In general, you could create a measure like:
Measure = SUM([Activity])
You put that in a tale along with GL ID and probably use a slicer on Trans Date or Timeline slicer custom visual.
As for your #2 issue, not sure what you are looking for here, is the problem that you do not have the Beg Balance?
Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.
Click here to read more about the July 2021 Updates
Did you know that you can visit the Power Query Forum in Power BI and now Power Apps