Daily Closing Balance

[Anonymous]

 

I am trying to visualise bank balance trend, i am facing error while trying to create powerbi dax for this.

My data is in the following table format:

Date,closing_balance,bank_name
2023-05-25 00:00:00,1356.36,SBI
2023-05-24 00:00:00,133551.92,HDFC
2023-05-23 00:00:00,119257.15,HDFC
2023-05-22 00:00:00,124573.15,HDFC
2023-05-19 00:00:00,124583.15,HDFC
2023-05-18 00:00:00,104639.15,HDFC
2023-05-18 00:00:00,7856.36,SBI

The dates with where no transactions were done are missing, also when in matrix and trying to separte by bank , there are null spaces which are considered as 0 for trend. i want to fill the null spaces by the last closing balance. How to? i have tried the following.

Balance Measure = 
VAR CurrentDate = Bank[transaction_date]
VAR BankName = Bank[bank_name]
RETURN
    CALCULATE(
        LASTNONBLANK(
            Bank[balance],
            1
        ),
        FILTER(
            ALLEXCEPT(Bank, Bank[bank_name]),
            Bank[bank_name] = BankName
                && Bank[transaction_date] <= CurrentDate
        )
    )

[Anonymous · Accepted solution]

Hi @Anonymous ,

I suggest you to create a calendar table and inactive the relationship between two tables.

Measure:

Balance Measure = 
VAR _Date =
    CALCULATE (
        MAX ( 'Calendar'[Date] ),
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
                && CALCULATE (
                    SUM ( Bank[closing_balance] ),
                    USERELATIONSHIP ( Bank[transaction_date], 'Calendar'[Date] )
                )
                    <> BLANK ()
        )
    )
RETURN
    CALCULATE (
        SUM ( Bank[closing_balance] ),
        FILTER ( Bank, Bank[transaction_date] = _Date )
    )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , You should use date table joined with date of your table in visual and meausre

example

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all('Date'),'Date'[date] <=max('Date'[date])))

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=max(date[Date])))

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.