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,

Something like this?

Hi PS_78,

Thank you for engaging with the Microsoft Fabric Community Forum.

Create a Calendar Table

To support date-based logic, create a calendar table.  
Go to Modeling > New Table and paste the following DAX:

Calendar = 
ADDCOLUMNS(
    CALENDAR(DATE(2023, 1, 1), DATE(2024, 12, 31)),
    "Year", YEAR([Date]),
    "Month", FORMAT([Date], "MMM"),
    "Month Start", DATE(YEAR([Date]), MONTH([Date]), 1)
)

Once created, go to Modeling > Mark as Date Table and select the Date column from this new table.

Create a Relationship

Now connect your main data table (Transactions) to this Calendar table:

Drag the Date column from Transactions to Calendar[Date].
Ensure this is a many-to-one, single-direction relationship.

Create Measures

In the Transactions table, create the following DAX measures one by one:

1. Debits

Debits = SUM(Transactions[Debit])

2. Consumption

Consumptions = SUM(Transactions[Consumption])

3. Beginning Balance

Beginning Balance = 
VAR FirstOfYear = MIN('Calendar'[Date])
RETURN
CALCULATE(
    SUM(Transactions[Debit]) - SUM(Transactions[Consumption]),
    FILTER(
        ALL('Calendar'),
        'Calendar'[Date] < FirstOfYear
    )
)

4. Net Change

Net Change = [Debits] - [Consumptions]

5. Balance

Balance = 
VAR FirstOfYear = MIN('Calendar'[Date])
VAR CurrentDate = MAX('Calendar'[Date])
VAR PriorBalance =
    CALCULATE(
        SUMX(
            FILTER(
                ALL('Calendar'),
                'Calendar'[Date] < CurrentDate &&
                YEAR('Calendar'[Date]) = YEAR(CurrentDate)
            ),
            [Net Change]
        )
    )
RETURN
IF(
    YEAR(CurrentDate) = YEAR(FirstOfYear) &&
    MONTH(CurrentDate) = MONTH(FirstOfYear),
    [Beginning Balance] + [Net Change],
    [Beginning Balance] + PriorBalance + [Net Change]
)

Build the Matrix Visual

Set the following fields:

Rows: Calendar[Month Start]  
Columns: (Optional) Supplier Name, Purchasing Document, WBS  
Values:  
   - Beginning Balance  
   - Debits  
   - Consumption  
   - Balance  

Add a Slicer for Year Selection

Use the field: Calendar[Year].
Select the year you want to analyze (e.g., 2025).

I have included the PBIX file that I created using the provided sample data. Kindly review it and confirm whether it aligns with your expectations.

If this solution worked for you, kindly mark it as Accept as Solution and feel free to give a Kudos, it would be much appreciated!

Hi,

PBI file attached.

Hope this helps.

Hi PS_78,

We wanted to follow up to see if our suggestion was helpful. Please let us know how things are progressing and if you are still encountering any issues.

If the response resolved your problem, you may mark it as the solution and give it a thumbs up to assist others in the community as well.

Thank you.

Thanks everyone for responding on this. I got stuck up in a production issue. I'll take this up in a week or so. Once I work on this, I would definitely give my feedback here.

Thanks again !