Forum Discussion

tcasekpi's avatar
tcasekpi
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's avatar
    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.

     

v-eachen-msft's avatar
v-eachen-msft
Community Support
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.