Beginning Balance Rollforward

Hi PS_78 ,

Achieving a beginning balance rollforward is a classic scenario in financial reporting and is entirely possible to build in Power BI using DAX formulas. The process involves preparing your data, creating a calendar table, and then writing a few key measures to handle the calculations.

First, you'll want to ensure your data is in a clean, tabular format. For example, your raw data can be structured into a CSV file. It's important to handle any non-numeric values in your debit and consumption columns so they can be properly summed.
After loading your data, it's critical to have a proper date column to use Power BI's time-intelligence functions. If you don't have one, you can create it in the Power Query Editor by adding a custom column. This formula constructs a valid date from your 'Posting Year' and 'Posting Period' columns.

= #date([Posting Year], 
    List.PositionOf({"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"}, [Posting Period]) + 1, 
1)

For the most reliable time-based calculations, a dedicated Calendar table is a best practice. You can create one from the "Modeling" tab using "New Table". This DAX expression will generate a comprehensive calendar. Once created, go to the Model view to form a relationship between this new Calendar table and your data table on their respective date columns.

Calendar =
ADDCOLUMNS (
    CALENDARAUTO(),
    "Year", YEAR ( [Date] ),
    "Month", FORMAT ( [Date], "mmm" ),
    "MonthNumber", MONTH ( [Date] )
)

With the data model in place, you can write the DAX measures. First, create a measure to calculate the "Beginning Balance" by finding the net total of all debits and consumptions from years prior to the year being viewed in your report.

Beginning Balance =
VAR MinYear = MIN('Calendar'[Year])
RETURN
CALCULATE(
    SUM(rollforward_data[Payment Posting - Debits]) - SUM(rollforward_data[Consumption]),
    FILTER(
        ALL(rollforward_data),
        YEAR(rollforward_data[Date]) < MinYear
    )
)

Next, a simple helper measure for the "Monthly Net Change" will make the final formula cleaner. This just subtracts the total consumption from the total debits for the given month.

Monthly Net Change = SUM(rollforward_data[Payment Posting - Debits]) - SUM(rollforward_data[Consumption])

Finally, the primary "Ending Balance" measure brings it all together. It establishes the balance at the start of the year and then cumulatively adds the net change for each month as it progresses through the year. This provides the running total you need.

Ending Balance =
VAR CurrentDate = MAX('Calendar'[Date])
VAR StartOfYear = STARTOFYEAR('Calendar'[Date])
VAR BalanceAtStartOfYear = [Beginning Balance]
VAR MonthlyChanges =
    CALCULATE(
        [Monthly Net Change],
        FILTER(
            ALL('Calendar'),
            'Calendar'[Date] >= StartOfYear && 'Calendar'[Date] <= CurrentDate
        )
    )
RETURN
    IF(
        ISBLANK([Monthly Net Change]),
        BLANK(),
        BalanceAtStartOfYear + MonthlyChanges
    )

To see the result, place a Matrix visual on your report canvas. Use 'Year' and 'Month' from your Calendar table on the rows, and add the Beginning Balance, Monthly Net Change, and Ending Balance measures to the values section to display the complete rollforward calculation.

Best regards,