topic Re: Cumulatively subtract from the current day in DAX Commands and Tips

Cumulatively subtract from the current day

Anonymous — Fri, 05 Feb 2021 18:43:18 GMT

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…

Re: Cumulatively subtract from the current day

mahoneypat — Fri, 05 Feb 2021 23:50:08 GMT

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

Re: Cumulatively subtract from the current day

Anonymous — Sat, 06 Feb 2021 02:03:06 GMT

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

Re: Cumulatively subtract from the current day

mahoneypat — Sat, 06 Feb 2021 23:32:45 GMT

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