Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Power BI Report (NetAm...
Finance | Project Operations, Human Resources, ...
Suggested answer

Power BI Report (NetAmount Actual vs Budget Amount dataset) using Analytical Accounting, Budget Tree / Budget data and actual Net amount

(1) ShareShare
ReportReport
Posted on by jgdynamicsBI 25

I want to be able to get a data model using Analytical Account Budget Tree / Budget Data for BudgetID 2020, against Actual Net Amount from GL?

Is their any sql queries to help me join to two together, or a bridge table?

Bellow is some of the things I done so far? Any advice would be welcomed..

Has anyone gotten a sql view be able to create a data model in power bi, for Budget Amounts (Budget allocation for projects Dimensions) VS NetAmount (actual amount spent)?
So far I only been able to do this model. using multiple sql queries for different tables in my model.
But I notice that I can't match actual Net Amount to Budget Tree Budget Amounts?

I am also using some Dax Measures for Budgets vs Actual   

Budget V Actual = 'Budget Measures'[Budget Allocations 2] - 'Budget Measures' [Total Acutals]

Total Actuals = SUM('Project Data'[NetAmount])'

Budget Allocation 2 =
VAR DaysInContext = COUNTROWS( Dates)
VAR DaysInMonth = CALCULATE( COUNTROWS( Dates ), ALL( Dates ), VALUES( Dates[Month & Year]) )
VAR DaysInQuater = CALCULATE( COUNTROWS( Dates), ALL( Dates ), VALUES(Dates[Quater]))
VAR DaysInYear = CALCULATE( COUNTROWS( 'Dates'), ALLEXCEPT( Dates, 'Dates'[Year] ))
VAR CurrentMonth = SELECTEDVALUE(Dates[MonthName] )
VAR MonthlyBudgetAmounts =
CALCULATE( [Total Budget], TREATAS( VALUES('Project Data'[MonthName]), 'Project Data'[MonthName]) )

Return
IF( OR( HASONEVALUE(Dates[Date]), HASONEVALUE( Dates[Month & Year] ) ),
DIVIDE( DaysInContext, DaysInYear, 0) * MonthlyBudgetAmounts,
[Total Budget])


Untitled-34.png

Untitled-35.png

Categories:
Microsoft Dynamics GP
  • jgdynamicsBI Profile Picture
    jgdynamicsBI 25 on at
    RE: Power BI Report (NetAmount Actual vs Budget Amount dataset) using Analytical Accounting, Budget Tree / Budget data and actual Net amount

    Want to create Budget v Actual report.

    Attached is a screenshot of Table joins of AAG00902, AAG00903, and AAG00905.
    And a short snippet of the tables joined and data extracted.

    But with AAG00905 I don't get the Budget Balance and ACT.

    I want to get the Account dimension Codes the Budget Amount (balance) and some how join to Actual NetAmount (Debit - Credit).

    SELECT
       dbo.AAG00903.aaBudgetID,
       dbo.AAG00903.aaBudget,
       dbo.AAG00903.aaBudgetDescr,
       dbo.AAG00903.YEAR1,
       dbo.AAG00902.aaCodeSequence,
       dbo.AAG00902.aaLvlCodeString,
       ISNULL(dbo.AAG00905.aaFiscalPeriod, 0) AS aaFiscalPeriod,
       ISNULL(dbo.AAG00905.Balance, 0)        AS Balance,
       ISNULL(dbo.AAG00905.ACTINDX, 0)        AS ACTINDX,
       dbo.AAG00902.aaBudgetTreeID
FROM dbo.AAG00902
         INNER JOIN
     dbo.AAG00903 ON dbo.AAG00902.aaBudgetTreeID = dbo.AAG00903.aaBudgetTreeID
         INNER JOIN
     dbo.AAG00905;
     ON dbo.AAG00903.aaBudgetID = dbo.AAG00905.aaBudgetID AND dbo.AAG00902.aaCod
    AGG00903_2D00_905.png

    The Above code does not provide anything.

    But the code below  will provide me  Account Dimension Code  and Budget (balance), but no way to join to Account Dimension Net Amount (Debit - Credit).

    SELECT rtrim(BudgetData.aaTrxDimCode) [Project], 
	SUM(BudgetData.Balance) [Project Budget], 
      rtrim(BudgetData.YEAR1) [Year]
FROM
(
    SELECT 1 AS REPTYPE, 
           AAG00903.aaBudgetID, 
           AAG00903.aaBudget, 
           AAG00903.aaBudgetDescr,
		   AAG00903.YEAR1,
           --ISNULL(AAG00903.YEAR1, '2020') [YEAR1],
           AAG00900.aaBudgetTreeID, 
           AAG00900.aaBudgetTree, 
           AAG00900.aaBudgetTreeDescr, 
           AAG00901.aaTrxDimID, 
           AAG00400.aaTrxDim, 
           AAG00400.aaTrxDimDescr, 
           AAG00901.aaOrder, 
           AAG00902.aaTrxDimCodeID, 
		   AAG00401.aaTrxDimCode,
           --ISNULL(AAG00401.aaTrxDimCode,'No Project') [aaTrxDimCode], 
           AAG00401.aaTrxDimCodeDescr, 
           AAG00902.aaCodeSequence, 
           AAG00902.aaLvlCodeString, 
		   AAG00904.aaFiscalPeriod,
		   AAG00904.aaActualPriliminary,
		   AAG00904.Balance AS Balance
           --ISNULL(AAG00904.aaFiscalPeriod, 0) AS aaFiscalPeriod, 
           -- ISNULL(AAG00904.aaActualPriliminary, 0) AS aaActualPriliminary, 
           --ISNULL(AAG00904.Balance, 0) AS Balance 
          
    FROM AAG00900
         INNER JOIN AAG00901 ON AAG00900.aaBudgetTreeID = AAG00901.aaBudgetTreeID
         INNER JOIN AAG00902 ON AAG00901.aaBudgetTreeID = AAG00902.aaBudgetTreeID
         INNER JOIN AAG00400 ON AAG00901.aaTrxDimID = AAG00400.aaTrxDimID
         INNER JOIN AAG00401 ON AAG00902.aaTrxDimCodeID = AAG00401.aaTrxDimCodeID
                                AND AAG00400.aaTrxDimID = AAG00401.aaTrxDimID
         INNER JOIN AAG00903 ON AAG00902.aaBudgetTreeID = AAG00903.aaBudgetTreeID
         FULL OUTER JOIN AAG00904 ON AAG00903.aaBudgetID = AAG00904.aaBudgetID
                                     AND AAG00902.aaCodeSequence = AAG00904.aaCodeSequence
    
) AS BudgetData
WHERE BudgetData.aaTrxDimCode IS NOT NULL
GROUP BY BudgetData.aaTrxDimCode , BudgetData.YEAR1
ORDER BY BudgetData.aaTrxDimCode;

  • Suggested answer
    agaber Profile Picture
    agaber on at
    RE: Power BI Report (NetAmount Actual vs Budget Amount dataset) using Analytical Accounting, Budget Tree / Budget data and actual Net amount

    Hello-

    Have you reviewed all of the AAG00900 tables which store the budget tree data for AA? Maybe you can use a field(s) in these tables (AAG00900 through AAG00906) that might help?  

    Adam G.

    Microsoft Dynamics GP

  • jgdynamicsBI Profile Picture
    jgdynamicsBI 25 on at
    RE: Power BI Report (NetAmount Actual vs Budget Amount dataset) using Analytical Accounting, Budget Tree / Budget data and actual Net amount

    Thanks, This is the first time posting here. :)

  • Beat Bucher GP Geek GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,021 Super User 2024 Season 1 on at
    RE: Power BI Report (NetAmount Actual vs Budget Amount dataset) using Analytical Accounting, Budget Tree / Budget data and actual Net amount

    Hi JG,

    I'm going to tag here Belinda Allen & who's is well versed in PowerBI Desktop and Dynamics GP.. she might have a good idea on how to tackle this challenge.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans