Relate General Ledger Entries and G/L Budget Entries in Excel/Power Pivot with Odata (NAV Business Central)

(0) Share

Report

Posted on by rabert

Hi!

I am trying to create a relationship/comparison between budgeted and actual amounts in Excel/Power Pivot with Odata from Budget (Page 120 G/L Budget Entries) and General ledger (Page 20 General Ledger Entries) for G/L Account No, Global dimension 1 and Date. However, when importing the above pages in Power Pivot I don't know how to create the relationship for Budget vs Actual for a specific account no, global dimension and date, as in the picture below - General Ledger and Budget appear as separate "sources" when i'm creating the Pivot Table and thus they cannot be compared

I have also done an import of page DimensionSetEntries and Chart of Accounts. Do any of you know how I can create a relationship and compare Actual Amount with Budgeted amount from the above Odata-pages in PowerPivot?

I have the same question (0)

All responses (1)

Answers (0)

Hrvoje Kusulja 372 on at

Like (0)

Report
Copy link

Link copied!
I am also interested in this topic.

I am using Power BI , and have gathered Data using Power Query from Dynamics 365 Business Central.

I have tables:

Accounts, General Ledger Entries, Budget Entries, Dimensions (Cost centers), DimTable

But also unsure how to relate, i did not find anything.

One option / direction is to create a new Dax table with comparison, like:

ActualsVsBudget = FILTER( CROSSJOIN( SELECTCOLUMNS('G/L Entries', "Account", 'G/L Entries'[Bal Account No], "Posting Date", 'G/L Entries'[Posting Date], "ActualAmount", -1*'G/L Entries'[Amount], "ActualCostCenter", 'G/L Entries'[Global Dimension 1 Code], "Actual Description", 'G/L Entries'[Description] ), SELECTCOLUMNS('G/L Budget Entries', "Budget Account", 'G/L Budget Entries'[G/L Account No], "Budget Date", 'G/L Budget Entries'[Date], "BudgetAmount", 'G/L Budget Entries'[BudgetAmount], "BudgetCostCenter", 'G/L Budget Entries'[Global Dimension 1 Code], "Budget Description", 'G/L Budget Entries'[Description] ) ), [Account] = [Budget Account] && ([ActualCostCenter] = [BudgetCostCenter] || [BudgetCostCenter] = "" || [ActualCostCenter] = "") )

but is not finished solution.
Awaiting for other expiriences and best practices..

Was this reply helpful? Yes No