Sum of most recent values for company in KPI object

[rob_vander]

Hi All,

I have below dataset

Date	Company	Value
1-Jan-24	a	100
1-Jan-24	b	60
1-Jan-24	c	90
1-Jan-24	d	100
1-Jan-24	e	85
1-Jan-24	f	110
1-Jan-24	g	200
1-Mar-24	a	60
1-Mar-24	b	100
1-Mar-24	c	70

I want to see the sum of latest values for each company. In my case it will be 1-Jan-24 for d,e,f,g (because for these companies only single record is available) and 1-Mar-24 for companies a, b and c( ignore 1-Jan-24 because latest dates is 1-Mar-24 for these companies). So total sum of values will be (100+85+110+200+60+100+70 =725. I want to show this value in KPI.

Could someone help me create a set analysis measure?

[rubenmarin · Accepted solution]

Hi, you can get that result using: Sum(Aggr(FirstSortedValue(Value,-Date),Company))

[Kushal_Chawda · Accepted solution]

@rob_vander  one more option

sum(aggr(if(Date = max(total <Company> Date),Value),Company,Date))