Cumulative balance of previous month

DAX DAX Calculations
1

check.pbix (191.2 KB)

image

I want the opening balance of current month to be TB Amount Total,
For other months it should be Cumulative Balance of previous month.
I am using the below formula , but not working

Opening Balance = IF (
YEAR ( SELECTEDVALUE ( ‘Date’[Date] ) ) = YEAR ( TODAY () )
&& MONTH ( SELECTEDVALUE ( ‘Date’[Date] ) ) = MONTH ( TODAY () ),
[TB Amount Total],
CALCULATE (
[Cumulative Balance],
PARALLELPERIOD ( ‘Date’[Date], -1, MONTH )
)
)

2 
Opening Balance =
VAR CurrentDate =
    TODAY ()
VAR CurrentMonth =
    MONTH ( CurrentDate )
VAR CurrentYear =
    YEAR ( CurrentDate )
VAR Result =
    IF (
        CurrentMonth = SELECTEDVALUE ( 'Date'[MonthOfYear] )
            && CurrentYear = VALUE ( SELECTEDVALUE ( 'Date'[Year] ) ),
        [TB Amount Total],
        [Cumulative Balance]
    )
RETURN
    Result
3

IF (
CurrentMonth = SELECTEDVALUE ( ‘Date’[MonthOfYear] )
&& CurrentYear = VALUE ( SELECTEDVALUE ( ‘Date’[Year] ) ),
[TB Amount Total],
[Cumulative Balance]

If not current Month , it should be cumulative balance of previous month

4

Hi @Anu

You can use below DAX.

    Opening Balance = 
IF (
    MONTH ( TODAY() )= SELECTEDVALUE ( 'Date'[MonthOfYear] )
        && YEAR ( TODAY() )= VALUE ( SELECTEDVALUE ( 'Date'[Year] ) ),
    [TB Amount Total],
    CALCULATE (
        [Cumulative Balance],
        PARALLELPERIOD ('Date'[Date], -1, MONTH )
        )
)

image
image839×313 24.5 KB

5

image
Opening balance for Nov 2020 should be 15.08 not -0.04

6

@AntrikshSharma any idea?

7

@Anu Like this?

image
image1028×361 22.9 KB

Measure =
VAR TimeTravel =
    CALCULATE ( MAX ( 'Date'[Date] ), DATEADD ( 'Date'[Date], -1, MONTH ) )
VAR CurrentDate =
    TODAY ()
VAR CurrentMonth =
    MONTH ( CurrentDate )
VAR CurrentYear =
    YEAR ( CurrentDate )
VAR Result =
    IF (
        CurrentMonth = SELECTEDVALUE ( 'Date'[MonthOfYear] )
            && CurrentYear = VALUE ( SELECTEDVALUE ( 'Date'[Year] ) ),
        [TB Amount Total],
        CALCULATE (
            [Balance Current Month],
            FILTER ( ALLSELECTED ( 'Date' ), 'Date'[Date] <= TimeTravel )
        )
    )
RETURN
    Result
4 Likes