Beginning Balance Rollforward

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!