Retreiving Year-end Trial balance for all of the entities under one enviroment

(1) Share

Report

Posted on by MU-

How can I get figures for Trial balance as of year end using PowerBI desktop.

As of now when going into V2.0 and then selecting "trial balances", it puts out the current balances as of YTD with entries made in the current year. However, what I am looking for is to get an Trial balances in PowerBI desktop that would show balances as of 31/12/2022 for all of the entities at once?

Thank you

I have the same question (0)

All responses (6)

Answers (0)

Sort by

DAnny3211 11,423 Super User 2026 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!

hi

you could call up from power BI the url to the trialbalance API like this

GET https://{businesscentralPrefix}/api/v2.0/companies({id})/trialBalances?$orderby number&$filter=dateFilter ge 2019-01-01 and dateFilter le 2019-12-31

applying filters &$filter=dateFilter ge 2019-01-01 and dateFilter le 2019-12-31

check my answer if it helped you, thanks

DAniele

Was this reply helpful? Yes No
MU- 5 on at

Like
a
(0)

Report
Copy link

Link copied!

Apologies, not sure if I follow.

In the powerBI desktop you mean to connect using Trial balance API link?

Initially what I am trying to get is the chart of accounts balances per 31-12-2022.

Thank you for your help

Was this reply helpful? Yes No
DAnny3211 11,423 Super User 2026 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!

I mean like this

DAniele

Was this reply helpful? Yes No
MU- 5 on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you for your help. Just to clarify should I use OData link or Soap URL? Also, it seems like that would display trial balance only for one entity, is there a way to make it so it displays for all?

Once, I get to this point, what should I do so it shows the balances per 31-12-2022?

I appreciate your help and patience

Was this reply helpful? Yes No
DAnny3211 11,423 Super User 2026 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

the parameter in red allows you to filter the dates

https://{businesscentralPrefix}/api/v2.0/companies({id})/trialBalances?$orderby number&$filter=dateFilter ge 2019-01-01 and dateFilter le 2019-12-31

look this for filter

https://learn.microsoft.com/en-us/dynamics-nav/using-filter-expressions-in-odata-uris

check my answer if it helped you, thanks

DAniele

Was this reply helpful? Yes No
MU- 5 on at

Like
a
(0)

Report
Copy link

Link copied!

1) When using the SOAP Url from Business Central i get a table below, should I use Odata link isntead?

2) When using Odata link I get TB displayed as of today, and then i guess i should insert the filter you are referring to right? When using the filter you shared I get an error like this.

Thank you for your help, it would be great if you could guide me further

Was this reply helpful? Yes No