How do I calculate the sum of a field depending on the most recent date?

[#1]

Hi all,

So I am trying to make a bar chart that will show me the total cost by Project Sponsor. The problem is that I want it to only calculate depending on the latest date for that project flash report.

Project Number	Name	Date	Project Sponsor
1	Test 1	01/02/2014	Ben
2	Test 2	01/02/2014	Luke
3	Test 3	01/02/2014	Luke
4	Test 4	02/02/2014	Adam

Project Number	Date	Cost
1	03/03/2014	100
1	05/04/2014	200
2	01/03/2014	400
2	09/04/2014	150
2	02/05/2014	340
3	14/03/2014	600
3	20/04/2014	700
4	01/03/2014	230
4	08/04/2014	250

I have something like the above. The two tables are linked by the Project Number. So if I calculate the sum of Cost only with the max date, the results I would expect are: Ben would have a total of 200. Luke would have a total of 1040. And Adam would have a total of 250.

So to make it clear, each person will have a number of projects and the cost changes every month depending on what they spend. So i want to get the total cost for each person. The person being the dimention.

Many thanks,

Bruno

[#2]

Attached with answer.

Thanks

AJ

[maxgro · Accepted solution]

try with

sum(

     aggr(FirstSortedValue(total <[Project Sponsor], [Project Number]> Cost, -Date),

          [Project Sponsor], [Project Number])

)

[#4]

Your script should be

Load * inline
[
Project Number, Name, Date, Project Sponsor
1, Test 1, 01/02/2014, Ben
2, Test 2, 01/02/2014, Luke
3, Test 3, 01/02/2014, Luke
4, Test 4, 02/02/2014, Adam
];

Load * inline
[
Project Number, Date1, Cost
1, 03/03/2014, 100
1, 05/04/2014, 200
2, 01/03/2014, 400
2, 09/04/2014, 150
2, 02/05/2014, 340
3, 14/03/2014, 600
3, 20/04/2014, 700
4, 01/03/2014, 230
4, 08/04/2014, 250
];

in Dimension use Project Sponsor

and in Expression use

=sum(aggr(FirstSortedValue(total <[Project Sponsor], [Project Number]> Cost, -Date1), [Project Sponsor], [Project Number]))

you will get

Vinay

[#5]

Thanks guys,

They all work.

=sum(aggr(FirstSortedValue(total <[Project Sponsor], [Project Number]> Cost, -Date1), [Project Sponsor], [Project Number]))

The one above is the one I was looking for, thank you

Kind regards,

Bruno Pereira