Pick only value for latest date

[akpofureenughwu]

Good day one

I have a set of data I need to pick only the latest data

Location	01/01/2017	02/01/2017	03/01/2017
Area 1	150	100	120
Area 2	100	40	60
Area 3	70	100	150

I used the crosstable function to resolve my data

Something like this

At the backend

Fox:

CrossTable (Date, Value, 1)

LOAD [Location],

    [42736],

    [42737],

    [42738]

from	DAILY Sales.xlsx]

(ooxml, embedded labels, table is Sample);

FinalTable:

Load [Location],

	Date(Num#(Date)) as Date,
	Month(Num#(Date)) as Month,
	'Q' & Ceil(Month(Num#(Date))/3) as Quarter,
	MonthName(Num#(Date)) as MonthYear,
	Year(Num#(Date)) as Year,
	Day(Num#(Date)) as Day,
	Value

Resident Fox;

Drop Table Fox;

All I need is how I can only display value based on latest date

[akpofureenughwu]

FirstSortedValue(Aggr(Sum(Sales), Date, Location), -Aggr(Date, Date, Location))

stalwar1‌  Thank you sir, the above script worked well .

Pleae one more question... How can I fetch out the highest sales value per day .... from the above table

The highest  sales value per day = 250 (01.01.2017)

How can I generate this figure from the front end?

Thanks

[sunny_talwar · Accepted solution]

Highest sales value per day? Across all days you want the highest value? May be this

Max(Aggr(Sum(Sales), Date))