sql-serverssas-2012

How to Create budget table Using current Actual Table

I have Fact table with 6 metric columns and three date key columns remain all are foreign keys,I need design a Budget table based on that fact table like

1)Current year Budget =last year fact +10%(last year fact)

2)Current Quarter Budget =last Quarter fact +10%(last Quarter fact)

3)Current Month Budget =last Month fact +10%(last Month fact)

Solution

  • Does this pseudocode make any sense?

    SELECT
    DATEPART(YEAR, GETDATE()),
    YearFact * 1.1 AS YearBudget,
    QuarterFact * 1.1 AS QuarterBudget,
    MonthFact * 1.1 AS MonthBudget
FROM
    FactTable
WHERE
    DATEPART(YEAR, FactDate) = DATEPART(YEAR, GETDATE()) - 1;