How to find a total count of multiple row where one value is distinct filtered on latest date

[ToNo87]

I have a table like this

HashId (text) | EventTime (date/time)| Amount

dsfa3r43         12/12/2019 12:04:16     5

dsfa3r43         12/12/2019 12:04:16     5

as23radf         12/11/2019 12:04:16     3

dsfa3r43         12/10/2019 12:04:16     2

I want to do a total count of the amount, but only where there is a distinct HashId and the latest EventTime.

So the result should be: 8 based on the fact that I have two unique HashIds, and the latest dates gives me a total of 8.

[Vvelarde · Accepted solution]

@ToNo87

hi. a solution would be this:

LastSums =
SUMX (
    SUMMARIZE (
        Table1,
        Table1[HashId (text) ],
        "MAX", MAX ( Table1[ EventTime (date/time)] ),
        "AMOUNT", MAX ( Table1[ Amount] )
    ),
    [AMOUNT]
)

[ToNo87]

Thanks @Vvelarde, but wont the last MAX just give me the highest and not the total sum of all the values in the Amount column?

[Vvelarde]

@ToNo87

hi, the SUMX aggregate all the max value from each Hash id.

HashId (text) | EventTime (date/time)| Amount

dsfa3r43         12/12/2019 12:04:16     5

dsfa3r43         12/12/2019 12:04:16     5

as23radf         12/11/2019 12:04:16     3

dsfa3r43         12/10/2019 12:04:16     2

Max take the Max EventTime of dsfa3r43, that is 12/12/2019 12:04:16 and the Max Amount (5), if you want both (5+5) Just change Max to Sum. The same repeat to as23radf . (EventTime: 12/11/2019 12:04:16   and MaxValue  is 3)

Let me know if works.

[MattAllington]

I believe the following measure will work. You should test it though. 

=sumx(values(tablename[hashid]),calculate(calculate(sum(tablename[amount]),lastdate(tablename[eventtime]))))

note this data will not compress well. If the table is small (say less than 500k rows) it won't be an issue. If it is big table, the file size may be very large. 

[ToNo87]

Thanks, I got the following error though:

MdxScript(Model) (1, 140) Calculation error in measure 'Table'[Value]: A date column containing duplicate dates was specified in the call to function 'LASTDATE'. This is not supported.