Return the most recent amount for account with multiple records

[powerBIpeon]

I'm having trouble trying to sum the most recent amount for accounts with multiple records.

Here is the table

The total amount I want from that table would be 60 (company A = 40 & Company B = 20)

I'm able to do it in excel using this array formula =MAX(IF($B$2:$B$7=B2,$A$2:$A$7))=A2 then filtering it out by only "TRUE." However, i'm having a hard time transititioning that formula to powerBi

Thanks in advance!

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[v-piga-msft · Accepted solution]

Hi @powerBIpeon ,

If you want to use the solution of Phil_Seamark, you need create another measure with the formula below.

Is Latest Row Filter = 
VAR LatestDate =
    MAXX (
        FILTER ( 'Table1', 'Table1'[Account] = EARLIER ( 'Table1'[Account] ) ),
        'Table1'[Date]
    )
RETURN
    IF ( 'Table1'[Date] = LatestDate, 1, 0 )

Measure = CALCULATE(SUM(Table1[Amount]),FILTER('Table1','Table1'[Is Latest Row Filter]=1))

In addition, you also could achieve your desired output with the formulas below.

Table = 
    SUMMARIZE (
        'Table1',
        'Table1'[Account],
        "date", CALCULATE ( MAX ( 'Table1'[Date] ), ALLEXCEPT ( Table1, Table1[Account] ) ),
        "amount", 
            MAX ( 'Table1'[Amount] )
    )
Measure 2 = SUMX('Table',[amount])

Here is the output.

Best Regards,

Cherry

[powerBIpeon]

thank you! @v-piga-msft 

[powerBIpeon]

https://community.powerbi.com/t5/Desktop/Show-only-data-from-the-latest-date/td-p/442889

@Phil_Seamark I found this thread you answered which is what I'm looking for. I got it to work, but how would i add in a second critera?