Power BI Month-End Reporting for Singapore Finance Teams

To automate month-end reporting in Power BI, stop rebuilding the spreadsheet pack. Load the trial balance from your accounting system each close, join it to a finance-owned account mapping and a fiscal date table, write variance measures once, secure each entity with row-level security and schedule refresh in Singapore time. Review and sign-off then happen on one report, not in email attachments.

At a glance

• Source: trial balance by entity, account and period, exported or queried from your accounting system
• Model: one fact table, plus account mapping, entity and fiscal date tables
• Measures: actual, budget, variance, variance %, year to date and prior year
• Security: dynamic row-level security by entity, for users in the Viewer role
• Refresh: up to 8 scheduled refreshes a day on Pro, 48 on Premium Per User or Fabric capacity
• Sign-off: a status table written from the report, or an approval flow

What Power BI should and should not do at month-end

Power BI is the reporting layer for management accounts: profit and loss by entity, variance to budget, cost centre packs, and cash and working capital views. Journals, reconciliations and statutory accounts stay in your accounting system and close process. The goal is simple: nobody retypes or pastes a number after the ledger closes.

For a Singapore group with regional subsidiaries, decide early where currency translation happens. Take translated SGD amounts from your consolidation process rather than recalculating them in Power BI with a separate rate table. Two translation engines will eventually disagree, usually the night before a board meeting.

Step 1: Import the trial balance, not the report

Ask your systems team for a trial balance extract at account level with five columns: entity, account, period end date, amount in local currency and amount in SGD. Load period movements, not year-to-date balances, so every measure can simply add up. Balance sheet closing balances then come from a running total.

If the extract arrives as a file each month, save it to one SharePoint folder with a consistent file name and use Power Query's folder connector to combine every period. If the ledger lives in an on-premises database, you need an on-premises data gateway for scheduled refresh.

Next, build the account mapping as a small table that finance owns: account, report line, statement, sign and a "favourable when" flag. When someone opens a new account, the mapping must change too. A check measure catches the miss:

Unmapped Amount =
CALCULATE (
    SUM ( TB[Amount SGD] ),
    ISBLANK ( 'Account Map'[Report Line] )
)

Put it on a close-checks page. Anything other than zero stops the pack.

Step 2: Build a fiscal date table

If your financial year ends on 31 March, the date table needs fiscal year and fiscal period columns so that April is period 1. A DAX version:

Date =
VAR FYEndMonth = 3
RETURN
    ADDCOLUMNS (
        CALENDAR ( DATE ( 2023, 4, 1 ), DATE ( 2027, 3, 31 ) ),
        "Month Start", DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 1 ),
        "Fiscal Year", CONCATENATE ( "FY", YEAR ( [Date] ) + IF ( MONTH ( [Date] ) > FYEndMonth, 1, 0 ) ),
        "Fiscal Period", MOD ( MONTH ( [Date] ) - FYEndMonth - 1, 12 ) + 1
    )

Mark it as a date table and relate it to the trial balance on the period end date. For year to date, classic time intelligence takes a year-end string:

Actual YTD = TOTALYTD ( [Actual], 'Date'[Date], "3/31" )

Microsoft's TOTALYTD reference recommends writing the year end as month/day, and the string is read in the locale of the file, so test it against a period you have already reported. Power BI also offers calendar-based time intelligence, which defines fiscal calendars as model metadata, but it is in preview as of October 2026. For a pack that directors rely on, stay with the classic approach until it is generally available.

Step 3: Write the variance measures once

Actual = SUM ( TB[Amount SGD] )
Budget = SUM ( Budget[Amount SGD] )
Variance = [Actual] - [Budget]
Variance % = DIVIDE ( [Variance], ABS ( [Budget] ) )
Actual Prior Year = CALCULATE ( [Actual], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
Reported Actual =
    SUMX ( VALUES ( 'Account Map'[Sign] ), [Actual] * 'Account Map'[Sign] )

The sign column flips credits so revenue shows as a positive number in the profit and loss. Relate the budget table to the same account mapping and date table so every measure slices the same way.

Drive favourable and unfavourable colouring from the "favourable when" flag in the mapping, not from the sign of the number: a positive variance is good for revenue and bad for costs.

One gotcha: if your model uses inactive relationships with USERELATIONSHIP(), for example posting date versus document date, Microsoft warns that it can cause unexpected errors once row-level security is enabled. Test those measures under a security role before go-live.

Step 4: Secure entities with row-level security

Controllers should see their own entities; the CFO sees all of them. Keep an access table with user email and entity code, and define one role on the entity table:

'Entity'[Entity Code]
    IN CALCULATETABLE (
        VALUES ( 'Entity Access'[Entity Code] ),
        'Entity Access'[User Email] = USERPRINCIPALNAME ()
    )

Two rules catch most teams out. First, row-level security only restricts people with Viewer permissions; workspace Admins, Members and Contributors see everything. Give controllers the Viewer role or distribute the report as an app. Second, Test as role in the service evaluates dynamic rules with your own identity, so confirm the result by signing in as a real test user.

If the pack includes payroll, keep it at cost-centre level rather than per employee. The PDPA's protection obligation applies to personal data in reports just as it does in source systems.

Step 5: Schedule refresh around the close calendar

Set the semantic model's refresh time zone to UTC+08:00 Singapore, so the time slots match your close timetable. From Microsoft's scheduled refresh documentation:

• Power BI Pro allows up to 8 scheduled refreshes a day; Premium Per User, Premium and Fabric capacity allow up to 48.
• Refresh normally starts within 15 minutes of the slot, but can be delayed by up to an hour.
• Shared capacity refreshes must finish within two hours; Premium allows five.
• Four consecutive failures switch the schedule off, so add your finance systems mailbox to the failure email list.
• There is no monthly schedule option. Use Power Automate, or Fabric pipeline refresh templates, for month-end or cascading refreshes.
• Scheduled refresh pauses after two months with no report views, which can catch a quarterly pack. Open it before each close.

Step 6: Run checks and sign-off on the report

Build a close-checks page that must be green before anyone writes commentary:

• The trial balance nets to zero for each entity and period.
• The unmapped amount is zero.
• Closed periods have not changed since last month, compared against a saved snapshot.

TB Out of Balance =
    ROUND ( CALCULATE ( [Actual], REMOVEFILTERS ( 'Account Map' ) ), 2 )

For sign-off, translytical task flows, generally available since Microsoft's March 2026 Power BI update, let a button in the report call a Fabric user data function that writes to a Fabric SQL database. A controller selects the entity and period, selects Sign off, and the function records who signed and when; the report then shows each entity's status. This needs Fabric capacity. Without it, a Power Automate approval started from the report does the same job, with the history kept in your approvals log.

Finally, keep separate development and production workspaces so nobody edits the live pack during close. If you are weighing a move to Fabric capacity for these features, our Power BI to Fabric migration guide covers the trade-offs.

A typical first close on the new model

Run the old spreadsheet pack in parallel for one or two closes, reconcile every line, then retire it. Our article on when to move from Excel to Power BI helps you make the case internally, and Copilot in Excel for finance teams covers the analysis that sensibly stays in Excel.

How CertTulen Academy can help

CertTulen Academy runs live, MCT-led Power BI training with hands-on labs. Microsoft Power BI Data Analyst (PL-300) covers the modelling, DAX, row-level security and refresh skills in this guide, and includes an exam voucher, mock practice tests and lab access (see the course page for details). For a finance team, we can run a private cohort with labs built around a trial balance structure like yours. Contact us or WhatsApp +60 16-241 8686 for a batch that suits your time zone.