Please help

[Anonymous]

I've been trying to get the balance of a multiple banks accounts based on the the most recent date the balance was updated. however, everytime I try to do that it gives me the total of all the balance histories. Can someone help me on how to do this? I have 2 tables in Direct Query mode. One with Account Information and another with Account Balance History, which has the Account Number as well, Account Balances, and the effective date of when that balance was updated. If anyone could get back to me ASAP it would be greatly appreciated!

[gadielsolis]

Could you please provide more details on this? Specifically the measure you're using to calculate the balance for the most recent date and if possible some screenshots without compromising any sensitive information

[Anonymous]

I figured it out. However i run into the error of querying over 1000000 rows. When I go into the filter side menu and choose one account I can see that it does indeed work. However when I want the balance of all the accounts, the error shows up. If there is anyway to filter the data down more or optimize my DAX Query please let me know. here is my DAX query: 

LatestBalance =

CALCULATE(

    LASTNONBLANK(

        Balance[BalanceAmount],

        1

    ),

    FILTER(

        Balance,

        Balance[number] = Balance[Number]

    ),

    FILTER

        (Balance,

        Balance[Date] = MAX(Balance[Date])

        )

)