Creating measures to get the most recent account balance based off slicer date

[zoop]

Hi everyone,

I am currently stuck with a problem related to DAX and hoping that someone could please help out.

In my example I have a table of bank account balances, with the date ('Balance Date', format YYYYMMDD) that the balance was archived in the system. There are three different account types - Savings 1, Savings 2 and Chequing.

I am looking to create three measures - one for each bank account type, which will sum the balances based off a date that is selected in a slicer. The caveat is that only the most recent balance should be returned depending on the date in the slicer (e.g. if the slicer date is 20 June, I would only want to return the most recent balance for that account up to that date. The balance is a snapshot at a point in time).

It's worth noting that if an easier solution would result from changing the format of the 'Balance Date' to a proper date format and joining onto the DimDate table then that's fine, I have just provided an example of the data I currently have but it is malleable.

I have included a link to a sample workbook below with expected outputs to help better understand the issue. Any assistance would be greatly appreciated.

https://www.mediafire.com/file/ebi51sncnjhj1hx/Account_Balance_Example.pbix/file

Thanks in advance.

[v-cazheng-msft · Accepted solution]

Hi @zoop,

In fact, you don’t need build relationship between Account_Balances table and DimDate table.

Then, you need these two Measures.

Blance In Range = 
VAR selectedDateInt =
    VALUE (
        CALCULATE (
            MAX ( DimDate[DateInt] ),
            FILTER ( ALL ( DimDate ), DimDate[Date] = SELECTEDVALUE ( DimDate[Date] ) )
        )
    )
VAR maxDateEachAccountType =
    CALCULATE (
        MAX ( Account_Balances[Balance Date] ),
        FILTER (
            ALLEXCEPT (
                Account_Balances,
                Account_Balances[Customer],
                Account_Balances[Account Type]
            ),
            Account_Balances[Balance Date] <= selectedDateInt
        )
    )
var distinctCx=DISTINCTCOUNT(Account_Balances[Customer])
VAR BlanceInRange =
SELECTCOLUMNS(
    TOPN (
        distinctCx,
        FILTER ( Account_Balances, Account_Balances[Balance Date] = maxDateEachAccountType ),
        Account_Balances[Balance Date], DESC,
        Account_Balances[Balance], DESC
    ),
    "Balance_",
    [Balance]
)
return SUMX(BlanceInRange,[Balance_])

SumEachAccountType = SUMX(Account_Balances,[Blance In Range])

The result looks like this.

Also, attached the pbix file as reference.

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

Best Regards,

Community Support Team _ Caiyun

[amitchandak]

@zoop , Ideally you should join with date table. Lastnonblankvalue can help

Lastnonblankvalue(Table[Date]), Sum(Table[Balance]) )

https://community.powerbi.com/t5/Quick-Measures-Gallery/Firstnonblankvalue-and-Lastnonblankvalue/m-p...

[zoop]

Hi @amitchandak, thank you for your response.

Do you mean I should join to the date table differently to how I did in my sample workbook? I will have a look into Lastnonblankvalue and how I could use this in my solution. If you have any additional guidance on how I could use this exactly in my sample workbook it would be much appreciated.