Cumulatively subtract from the current day

[Antmkjr]

https://drive.google.com/file/d/1wJNNBhW5QY-4RGLd21lcYl15ECW20fvV/view?usp=sharing

The intention is to reverse calculate the opening balance from current balance (For Type =FC) till 2018 

2021 Feb 5 , TB balance = 436, so opening balance = 436-(-50) = 486
For Feb 4 , Amount =0 , so opening balance is still 486
For Feb 3, Amount = 100, so opening balance =486-100= 386 and so on…

Also when drilled up to the monthly level, it should show the opening balance for Feb 1 as Feb Month opening balance, opening balance for Jan 1 as Jan Month Opening balance etc.

At yearly level(drill up), the balance for Jan 1 2021 as 2021 opening balance… the opening balance for 2020 Jan 1 as 2020 opening balance and so on…

[mahoneypat · Accepted solution]

Replace TODAY() with DATE(YEAR(TODAY()),12,31).  But you will see results for all the remaining months for the current year.  You can adapt that logic further as needed (or remove it).

Pat

[mahoneypat]

Please try this measure expression

Open Balance =
VAR vMinDate =
    MIN ( 'Date'[Date] )
VAR vMaxDate =
    MAX ( 'Date'[Date] )
VAR vTB =
    CALCULATE (
        LASTNONBLANKVALUE (
            TB[Date],
            CALCULATE (
                SUM ( TB[TB Balance] )
            )
        ),
        ALL ( TB )
    )
VAR vCashFlow =
    CALCULATE (
        SUM ( Cashflow[Amount] ),
        ALL ( 'Date' ),
        Cashflow[Date] >= vMinDate
            && Cashflow[Date]
                <= TODAY ()
    )
RETURN
    IF (
        vMaxDate
            <= TODAY (),
        vTB - vCashFlow
    )

Pat

[Antmkjr]

It is fine except that for the highlighted part values are not coming.