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!