Forum Discussion

New Member

6 years ago

Solved

Rolling Balance Sheet Balance

Hello, I am new to Power BI and I am having trouble adding a cumulative column. I have a table that shows the actitvity by account by month that I want to use to show the ending balance eve...

Need Help

v-eachen-msft

6 years ago

Hi tcasekpi ,

You could create a date table as a date filter. Then refer to the following measure:

Measure =
VAR a =
    CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
VAR b =
    EOMONTH ( a, -12 )
RETURN
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Account] ),
            'Table'[End of Month] <= MAX ( 'Table'[End of Month] )
                && SELECTEDVALUE ( 'Table'[End of Month] ) <= a
                && SELECTEDVALUE ( 'Table'[End of Month] ) >= b
        )
    )

Here is my test file for your reference.

test.pbix71 KB

tcasekpi

New Member

6 years ago

I created it as a measure and use a dates table. What I am ultimately trying to do is take a transaction table and convert it to show 12 month rolling balance sheet balances based on a date slicer.

I see a number of examples that work for revenue accounts but none for balance sheet cumulative totals.