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.

     

tcasekpi's avatar
tcasekpi
New Member
6 years ago

Thank you for the reply. This causes a circular dependency in my table. I do not know how to resolve. 

andre's avatar
andre
Icon for Memorable Member rankMemorable Member
6 years ago

are you creating it as a column or a measure?  you should be doing it as a measure, also make sure you have a valid date table in your model