Finance - Payroll Tracking

Finance - Payroll Tracking

This sample provides insights from the payroll datasets produced at the end of this ETL article: Finance - Payroll Tracking, that show the Actual, Forecast, and Budgeted payroll and contract staff pay for a particular year.

Payroll Tracking Measures

The following specific measures are shown:

1. Budgeted Pay [GBP]
2. Actual/Forecast Pay [GBP]
3. Variance [GBP]
4. Variance [%]

Dashboard - Snippet

Click to view large-sized image

Open interactive version

Data Model - Snippet

Click to view large-sized image

Measures

Actual [GBP]

[Actual [GBP]]] = CALCULATE (
    SUM ( 'Actuals - Payroll (All Lines)'[Value] ),
    'Actuals - Payroll (All Lines)'[Expense Line] = "Salary"
)
    + CALCULATE (
        SUM ( 'Actuals - Payroll (All Lines)'[Value] ),
        'Actuals - Payroll (All Lines)'[Expense Line] = "Contract Staff"
    )

Actual/Forecast [GBP]

[Actual/Forecast [GBP]]] = VAR DateSelect = [Date Select]
VAR ActualForecast =
    IF (
        MAX ( 'Date List'[Date] ) <= DateSelect,
        [Actual [GBP]]],
        [Forecast [GBP]]]
    )
VAR TotalActuals =  SUMX(FILTER(ALL('Actuals - Payroll (All Lines)'[Month]), [Month] <= DateSelect), [Actual [GBP]]])  
VAR TotalForecast =  SUMX(FILTER(ALL('Forecasts - Payroll (Salary Line)'[Date]), [Date] > DateSelect), [Forecast [GBP]]])  
RETURN
    IF (
        ISFILTERED ( 'Date List'[Date] ),
        ActualForecast, TotalActuals + TotalForecast
    )

Budget [GBP]

[Budget [GBP]]] = CALCULATE ( SUM ( 'Budget - Payroll (Salary Line)'[Value] ) )

Date Select

Date Select = IF (
    HASONEVALUE ('Date List Selected'[Date]),
    VALUES ('Date List Selected'[Date]),
    1
)

Forecast [GBP]

[Forecast [GBP]]] = CALCULATE ( SUM ( 'Forecasts - Payroll (Salary Line)'[Value] ) )

Variance [%]

[Variance [%]]] = DIVIDE([Variance [GBP]]], [Budget [GBP]]], BLANK())

Variance [GBP]

[Variance [GBP]]] = [Budget [GBP]]] - [Actual/Forecast [GBP]]]

Calculated Columns

Business Area

Business Area = TRIM([Business - P&L]) & " - " & TRIM([Business - Department]) & " - " & TRIM([Business - Sub-Department])

Staff Reference

Staff Reference = TRIM([Employee Code]) & " - " & TRIM([Supplier Reference])