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.

     

andre's avatar
andre
Memorable Member
6 years ago

Well, if you are dealing with balancesheet logic, then you cannot use YTD and QTD functions or as balancesheet amounts are not additive, you should be using CLOSINGBALANCEMONTH(), CLOSINGBALANCEQUARTER() and CLOSINGBALANCEYEAR() functions for MTD, QTD and YTD.

 

CLOSINGBALANCEYEAR also has a parameter to deal with fiscal year not being the same as calendar year

 

example End Of Month Balance = CLOSINGBALANCEMONTH(sum(gl[amount]), calendar[dates])

 or something like that

  • tcasekpi's avatar
    tcasekpi
    New Member
    6 years ago

    Thank you for the reply, when applied this results in a column in my table that is equal to the amount already listed there.