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.

     

Jidnyasa2904's avatar
Jidnyasa2904
Helper I
1 year ago

Following the similar topic, I want the balance of the end date of any selected date via slicers. I have multiple slicers like Month, Week, WTD/YTD/FullYear. However, I have disabled the WTD/YTD/FullYear slicer. 

Balance for the selected year =
VAR lastdate= CALCULATE(
    MAX('Date'[Date]),
    FILTER('Date ', 'Date '[Year] = SELECTEDVALUE('Max Year'[Year]))
)

VAR Result = CALCULATE(
    SUM('BalanceData'[Cost]),
    'Date'[Date] <= lastdate,  -- Use the adjusted enddate (365 days ago)
    REMOVEFILTERS('WTD/MTD/YTD/FullYear'),
    REMOVEFILTERS('Date')
)
RETURN
IF(Result = 0, Blank(), Result)

I only have to consider the last date of the selected slicers and calculate the value from the beginning of the dataset.  The above formula gives me that, but I want an average of 364 days from the end date. For that, I want the balance of all 364 days.