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
dstramilov
Helper II
Helper II

General Ledger to Balance Sheet Financial Statement

 

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)

 

Table Structure

 

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)

 

 

 

13 REPLIES 13
frenchbrian
New Member

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.

I agree wholeheartedly.  The content pack is useless without the ability to dive in and extend it.

Eric, 

Hey - please reach out on linked in if you'd like to talk / share notes on this point: 

 

Jason

https://www.linkedin.com/in/jasonchroman/

 

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. 

10000% agree, PowerBi should allow to download online pack for further users customization. 

sdjensen
Solution Sage
Solution Sage

@dstramilov - did you read this article on sqlbi.com? It explains a bit about balance calculation and how you can make use of the new bidirectional relationship to improve performance.

 

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.

/sdjensen

I do appreciate above your suggestions, may be there is easier way to do this, like it was done in QuickBooks Online model way back?

 Capture.PNGCapture 3.PNG

 

 

Capture2.PNG

Hi @dstramilov,

 

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:

QuickBooks Online content pack for Power BI

 

If you have any question, please feel free to ask.

 

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.

Qiuyun_Yu,

 

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.

I am asking how "Balance Sheet Incl Retained Earnings" measure is created, what is DAX formula?

 

@dstramilov

 

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.

Greg_Deckler
Super User
Super User

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?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.