Cumulative balance of previous month

check.pbix (191.2 KB)

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 )
)
)

``````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``````

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

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 )
)
)
``````

Opening balance for Nov 2020 should be 15.08 not -0.04

@AntrikshSharma any idea?

@Anu Like this?

``````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