Beginning Balance Rollforward

[PS_78]

Hello Team - I have a requirement to show "Beginning Balance" Roll forward in a Power BI visual. Given below the sample data and expected output. Can you help me if this is achievable in Power BI?

RAW Data:

Supplier Name	Purchasing Document	Posting Year	Posting Period	WBS	Payment Posting - Debits	Consumption
ABC	PO1	2024	Dec	WBS1	$ 250,000.00	$         150,000.00
ABC	PO1	2024	Dec	WBS2	$ 120,000.00	$            40,000.00
DEF	PO2	2024	Dec	WBS3	$ 150,000.00	$            75,000.00
XYZ	PO3	2024	Dec		$ 320,000.00	$         210,000.00
ABC	PO1	2025	Jan	WBS1	$ 25,000.00	$            15,000.00
XYZ	PO3	2025	Jan			$                              -
ABC	PO1	2025	Feb	WBS2	$ 30,000.00	$            45,000.00
DEF	PO2	2025	Mar	WBS3		$            75,000.00

Expected Result: --> The Balance shown for each month should be "Beginning Balance (Sum of all prior year "Debits" - "Consumptions") " - ("Debits" for the specific month - "Consumption" for the same month). For example: January Balance should be "Beginning Balance" - ("Debits" for Jan - "Consumption" for Jan). February Balance should be January Balance - ("Debits" for Feb - "Consumption" for Feb). If Beginning Balance or Monthly Balance is 0 then, it should be just ("Debits" for that month - "Consumption" for that month).

Please help.

Thanks,

Phani

[PS_78]

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 !

[v-sgandrathi]

Hi @PS_78,

Thank you for the update

We completely understand that production priorities can be time-sensitive. Please feel free to take the time you need, and we’ll be here to assist whenever you’re ready to revisit the issue.

We appreciate your follow up, and we look forward to your feedback once you're able to resume work on this.

Best regards,
Sahasra
Community Support Team.

[v-sgandrathi]

Hi @PS_78,

Just wanted to check regarding your question. We haven’t heard back and want to ensure you're not stuck. If you need anything else or have updates to share, we’re here to help!
Thank you.

[v-sgandrathi]

Hi @PS_78,

Following up to see how things are going with your issue. Has the response provided resolved your query? We’d like to know if you need more help or if everything’s working fine now.

If resolved, please mark the answer as Accepted and drop a Kudos to guide fellow members.

Thanks for staying active in the Microsoft Fabric Community!

[v-sgandrathi]

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.

[PS_78]

Hello @v-sgandrathi - Appreciate your follow-up on this to understand if I needed further support. But I really could not spend time to look into this as I am stuck up with additional high priority tasks. I will take it up in a week or 10 days. Shall definitely update you once I start working on this.

Thanks,

PS 

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[v-sgandrathi]

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!

[lbendlin]

Something like this?

[PS_78]

Thanks @lbendlin . I would need to show Beginning Balance only once, which should be the balance from prior years.

This should be followed by "Debits", "Consumption" and "Balance" for each month in the current year or year selected in slicer. Formula for Balance should be (for the first month in the current year, Beginning Balance - difference between "Debits and Consumption". Subsequent months it should be Prior Months balance - difference between that months "Debits and Consumption".). If the Beginning Balance / Balance for a specific combination of PO, Supplier and WBS is 0, Balance for that month should be just the difference between Debits and Consumption for that specific month.

If it's not possible to show Beginning Balance only once, that is okay. I can exclude Beginning Balance but show only Debits, Consumption and Balance.

Thanks,

Phani

[DataNinja777]

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,

[PS_78]

Hello @DataNinja777 - Thanks for the detailed steps. I will work on this and get back to you.

I just tried this. It created table from 2023 only. Does this scan my dataset and creates the entries by scanning all date columns in all datasets in my PBIX file?

Calendar =

ADDCOLUMNS (

    CALENDARAUTO(),

    "Year", YEAR ( [Date] ),

    "Month", FORMAT ( [Date], "mmm" ),

    "MonthNumber", MONTH ( [Date] )

)

Thanks,

Phani

[lbendlin]

Does this scan my dataset and creates the entries by scanning all date columns in all datasets in my PBIX file?

Yes, yes it does. One of the reasons not to use CALENDARAUTO()