You’re offline. This is a read only version of the page.
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])
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).
Share
Report
All responses (
Answers (
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.aaCodThe 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;
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
Thanks, This is the first time posting here. :)
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.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
290,522
Super User 2024 Season 2
#2
Martin Dráb
228,441
Most Valuable Professional
#3
nmaenpaa
101,148