Need Sum of values for most recent date by dimension

[datagrrl]

I did find two similar posts, but they didn't address this exact question.

This seemed pretty simple at first, to just get the sum for the max date, but the problem is I need the sum for the max date for each value of the dimension. So while 11/23/22 is the max date, I will not have values for the other countries.

I thought something like this might work, but I think I am missing something obvious.

=Sum(Aggr(If(Date = Max(TOTAL <Country> Date), Sales),Date, Country))

Data:

Expected Results

TIA, Sample App Attached

[Aditya_Chitale]

try this:

test:
LOAD
Country,
"Date",
Sales
FROM [lib://Test1121/TestTable.xlsx]
(ooxml, embedded labels, table is Data);

left join (test)

Load
Country,
max("Date") as max_date
resident test group by Country;

use below expression in frontend expression:

Sum({<date={"$(=max_Date)"}>}sales)

Regards,

Aditya

[datagrrl]

I ended up getting what I expected for a single country with this

SUM({<Country={'Mexico'},Date={"$(=Max({<Country={'Mexico'}>}Date))"}>}Sales)

Since I need a KPI for a specific dimension value, this worked for me.

[vinieme12]

would rather avoid row level expression search entirely

left join (test)

Load
Country,
,max("Date") as Date

,1 as flag_maxDate
resident test group by Country;

use below expression in frontend expression:

Sum({<flag_maxDate={1}>}sales)