Data Model Pattern: Actuals and Budgets

10 Oct, 2024

Actuals and budgets

This can take many forms and data types: - Money - e.g. revenue, profit/gross margin, cost - Hours - e.g. chargeable hours, leave hours - Unit quantity

Fact table

I've found it's easiest to handle to combine actuals and budget all into one table.

The table will look something like this:

Transations

Actuals

Actuals (hours, money) usually come from the finance system. The time grain is usually per day, or per week.

Budget

The budget figures are normally set outside of the finance system, e.g. an Excel file. The time grain will usually be different to the actuals, e.g. the actuals could be daily, but the budget is monthly. For example, if the budget is set on a per-month basis, the date can be set to a fixed point in the month, like the start of the month, or the end of the month, like the example above.

This is fine as long as the transaction table is connected to a date/calendar table that accounts for all time grains.

Calendar

Measures

There are basic measures that can be done for actuals and budgets, and they can be based on a simple SUM().

Total Amount

Total Amount = 
    // this will be used as a base measure only, and not be brought into visuals
    SUM(Transactions[Amount])

Total Amount (Actuals)

Total Amount (Actuals) = 
    CALCULATE(
        [Total Amount],
        Transactions[Source] = "Actuals"
    )

Total Amount (Budget)

Total Amount (Budget) = 
    CALCULATE(
        [Total Amount],
        Transactions[Source] = "Budget"
    )

Variance

Usually we want to see a variance. The calculation depends on how you see the numbers (e.g. is more than budget good? Or is it bad?).

In this example, we'll pretend that the amount is revenue, where more is good.

Varaince Amount = [Total Amount (Actuals)] - [Total Amount (Budget)]

Visuals

Actuals and budget can be compared side by side by having the Source field in the Legend.