I’ve been trying to create a GI report listing all the Balance Sheet account code closing balances at each month end across multiple years. The aim is to export that via API to Power BI in a kind of clean CSV state i.e. no grouping, just rows and columns.
So I want one row for each account code and each month end date. My first attempt generated 3,600 rows so it’s not that large a set of data.
My attempt is 95% right but it’s missing key values and on reflection I think I’m using the wrong set of tables (or in the wrong way) but the documentation isn’t good enough to work that out.
The reason it is wrong is when I compare the output to the Balance Sheet Financial Statement report I get some differences, but I can’t see why they are different. I have realised that the report doesn’t give me balances at a month end if there are no transactions in the month. I can fix this through a Power Query custom function but sadly I am still getting some errors.
My model is as follows:
- tables: GL.Account, GL.FinPeriods.MasterFinPeriod, GL.GLHistory, GL.Ledger
- relations:
- GLHistory Inner FinPeriod: FinPeriodID = FinPeriodID
- GLHistory Inner Account: accountID = accountID
- GLHistory Inner Ledger: ledgerID = ledgerID
- conditions:
- GLHistory.BalanceType = Actual
- grouping:
- GLHistory.FinPeriodID
- Account.AccountCD
Can anyone point me in the right direction? Thanks!
