Need Help - How to sum revenue with critera

[ERing]

I have a requirement to have a card that displays revenue by date, however the revenue for Account Type = Existing should be summed with other records with the same Account ID according to the earliest Sale Date for records with the same Account ID.

Using the sample data below, selecting 10/1 should return $28,000 (highlighted rows). It would return the rows with sale date of 10/1/23, but also the rows where Account Type is Existing AND the Account ID matches records with the same Account ID of the selected Sale Date.

I tried doing this in PowerQuery by creating a new date column using my criteria, however I'm using Direct Query which prevented me from using that methoed.

Should I try to add a column in the report view? Can I build this logic into a measure?

Please help!

[amitchandak]

@ERing , Try a measure like

Sumx(filter(allselected(Table), Table[Account ID] = max(Table[Account ID])) , [Revenue])