Just find a report doing what you want and have a look at the Schema - eg. Trial Balance Detailed.

You’ll need to use GLHistoryByPeriod and GLHistory. But the way its done is a little tricky. GLHistory needs to be joined twice to GLHistoryByPeriod - one for current period activity and one for last activity period (ie. no activity in current period). When there’s no current period activity, you need to take the last activity period balance.

Thanks for your response I am trying to build the GI based on the Trial Balanced Detailed report.    Nearly there with it but still having a few issues with it not reporting the YTD figures on the lines

if you look at that report, the significant relations are GLHistoryByPeriod → GLHistory (alias AHCurrent) and the same join a 2nd time alias AHLast. AHCurrent only finds a row if there was activity in the specific period. AHLast will be the row with the Last activity (prior to current period). 

Take a look at the formula for Beginning Balance in groupHeaderSection2.

Thanks for all the replies I have managed to create a GI based on the Trial Balance Detailed report and got it working correctly

Hi @juneterris87

I am trying to create a similar GI, for the purposes of a dashboard. I would really love to see your GI to see your joins if you were willing to share this.
 

Hi I thought I had it working but when they rolled into the next year it didn't work correctly.   Recommending velixo now