Cumulative Balance issue

check (2).pbix (192.2 KB)

I want the Cumulative Balance Updated column to be the cumulative of Balance Current Month Updated

This formula seems to be not working
Cumulative Balance Updated =
VAR Total =[TB Amount Total] + [ToCollect] - [Forecasted payments v2]

VAR Result_in =
Total
+ IF (
Total < 0,
Total * -1
)
RETURN
CALCULATE (
Result_in,
FILTER ( ALLSELECTED( ( ‘Date’ )), ‘Date’[Date] <= MAX ( ‘Date’[Date] ) )
)

Also the below is not working

Cumulative Balance Updated =
CALCULATE (
[Balance Current Month Updated],
FILTER ( ALLSELECTED( ( ‘Date’ )), ‘Date’[Date] <= MAX ( ‘Date’[Date] ) )
)

Hi @Anu,

You can use below DAX code (Assume you want to start from Sep 2020 data of Balance Current Month Updated ignoring previous date)

Cumulative Balance Updated = 
VAR LastVisibleDate =
    MAX ( 'Date'[Date] )
VAR FirstVisibleDate =
    MIN ('Date'[Date] )
VAR LastDateWithSales =
    CALCULATE (
        MAX ( Collections[Date] ),
        REMOVEFILTERS ()
    )
VAR Result =
    IF (
        FirstVisibleDate<=LastDateWithSales,
        CALCULATE (
            [Balance Current Month Updated],
            FILTER ( ALLSELECTED ( 'Date' ), 'Date'[Date] <= LastVisibleDate )
            )
    )
RETURN
    Result

The Cumulative balance should propagate downwards, for May 2021, it should be 415.1M and so on.

Also could you please let me know what is wrong with the current formula

I need to have cumulative balance for the rest of the months also,currently its stopping at Apr 2021,
But it should propagate to all the years below .

if temp<0, it shouldn’t be blank, I didn’t understand why did you add this functionality
Temp <0, means payments ,it cannot be made 0

@AntrikshSharma

Hi @Anu

You can wait for other members answer.

check (2).pbix (192.7 KB)

Also if i change values for Funding , the above formula is not working

Any help on this is highly appreciated.

@Greg @Melissa

Thanks to the contributors of this post. Due to the length by which this post has been active, we are tagging it as Solved. For further questions related to this post, please make a new thread.