Calculating a latest balance within filtered period

[JRF_PowerBI]

Hi. I'm still relatively new to Power BI and have been a bit stumped by how to fulfil a request from one of my collleagues. Any help would be much appreciated!

Here is a small excerpt of the data set showing the relevant columns of data I'm working with:

The request I've had is to produce a report that returns a single 'Latest Balance' figure (based on the latest BalanceToDate) by AccountReference, which works with a YearMonth filter. So for example, if a user filtered YearMonth to April-2023, they would expect to see the following figures returned for the two accounts in the table above:
MILL0162_1 = £272.93 

RAND0044_1 = £736.56

I've played around with MAX and LASTDATE functions, but these always seem to be applied to the whole table, so I end up with a handful of Closing Balances for accounts where the BalanceToDate happens to be the last day of the month, but all other accounts, where the BalanceToDate are before the last day of the month, are getting filtered out. I need a function that will evaluate each account reference and find the latest BalanceToDate, based on the date filter. Is this possible?!

[v-rzhou-msft · Accepted solution]

Hi @JRF_PowerBI ,

I suggest you to create a DimDate table with YearMonth to help your calculation.

Calendar = ADDCOLUMNS(CALENDARAUTO(),"YearMonth",FORMAT([Date],"MMMM-YYYY"),"YearMonthSort",YEAR([Date]) * 100 + MONTH([Date]))

Measure:

Closing Balance on last day = 
VAR _SUMMAIRZE =
    SUMMARIZE (
        'Table',
        'Table'[AccountReference],
        "Balance",
            VAR _LASTDAY =
                CALCULATE ( MAX ( 'Table'[BalanceToDate] ) )
            RETURN
                CALCULATE ( SUM ( 'Table'[ClosingBalance] ), 'Table'[BalanceToDate] = _LASTDAY )
    )
RETURN
    SUMX ( _SUMMAIRZE, [Balance] )

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.

[JRF_PowerBI]

This worked perfectly, thanks for your help!

[DataNinja777]

Hi @JRF_PowerBI,

I am assuming the last balance is carried forward until the interval of the next balance date, like as shown in the visualization below:

In order to prepare the visualization above, I created separate dimension tables for calender table and account reference dimension table, and created a data model like as shown below:

Then, I wrote a dax measure below which was put in the visualization shown above:

I am attaching the link to the pbix file below:

https://1drv.ms/u/s!AlqFfzVTqicpnW3sVgclR52zn2iG?e=BzDrQr

[JRF_PowerBI]

Thanks @DataNinja777 - think your solution is just a variation on @v-rzhou-msft...I tried that one first and it worked, but no doubt your solution would have done too (the DAX in both looks pretty similar). Cheers anyway!