Opening & Ending Balances For Selected Periods

[muhitdinfa1]

Although it sounds simple, yet I am not able to have the correct values in my table.

I have tested different approcahes such as:

CB Option 1 =CLOSINGBALANCEMONTH(SUM('Table1'[Amount]),'Table1'[Date])   => NOT Returning Correct Ending Balance

CB Option 2 =CLOSINGBALANCEMONTH(SUM('Table1'[Amount]),'DimDate'[Date])   => Returning Correct Ending Balance

CB Option 3 =CALCULATE(Sum('Table'[AMOUNT]),LASTDATE('DimDate'[Date])) => Returning Correct Ending Balance

For CB Options 2 and 3, it is working if I am selecting the date range including January.

However, when I am unselecting January from the filter, Beginning Balance is returing only zeros.
OB Option 1 = OPENINGBALANCEMONTH(SUM('Table1'[Amount]),'DimDate'[Date])

OB Option 2 = [CB] -CALCULATE(SUM(Table1[Amount]),ALL(Table1[Date]))

Also, since my latest selected month is June, I would expect my Ending Balance to remain the same no matter what I am adding/removing prior to that latest month, but it is changing when I am selecting/unselecting anything prior to June. But with CB Option 1 which is not returing the correct amount, the Ending Balance is remaining as expected although with wrong value.

Any help would be appreciated!

[amitchandak]

@muhitdinfa1 , All-time intelligence function works best with a date table , because it need continuous dates.

OPENINGBALANCEMONTH is CLOSINGBALANCEMONTH of last month

CLOSINGBALANCEMONTH looks for the only end of the month date. but lastdate can last available date.

If this does not help

Can you share sample data and sample output in table format?

refer if these can help

https://www.youtube.com/watch?v=yPQ9UV37LOU

https://www.youtube.com/watch?v=6lzYOXI5wfo

[muhitdinfa1]

@amitchandak Thank you, but it is not quite helpful. Here's the sample data snapshot. Sometimes, I want to be able to filter by initials in a slicer filter. Unfortunately, it's not allwoing me to paste the values here.

[v-shex-msft]

HI @muhitdinfa1,

I'd like to suggest you use the date function to manually define the filter ranges on your table records if you want to achieve some advanced operations.

Time Intelligence "The Hard Way" (TITHW)  

Regards,

Xiaoxin Sheng