Calculate last value by date and id

[antoniopgouveia]

Hi all,

I have a list of opportunities where all changes to them are recorded. I want to be able to extract the last sales amount for a certain time period and for each opportunity, the issue being that there can multiple changes to the same opportunity. Below, I have a sample with the Opportunity ID, Creation Date and Sales amount:

Id	CreatedDate	Amount__hst
006D000000pOj4XIAS	03/04/2018 09:46	99257
006D000000rHnhtIAC	03/04/2018 23:57	116110
006D000000uHe3xIAC	03/04/2018 21:10	115000
006D000000xjhG7IAI	03/04/2018 11:50	80000
006D000000yLtscIAC	02/04/2018 20:11	110000
006D000000yLtscIAC	02/04/2018 20:56	113000
006D000000yLtscIAC	02/04/2018 20:56	113000
006D000000yLvrnIAC	03/04/2018 09:38	1
006D000000yLvrnIAC	03/04/2018 09:39	117300
006D000000yLy1uIAC	03/04/2018 22:34	115000
006D000000yLy1uIAC	03/04/2018 22:54	115000
006D000000yLy3MIAS	03/04/2018 22:38	100000
006D000000yLy3MIAS	03/04/2018 22:40	170100
006D000000yLy3MIAS	03/04/2018 22:43	175300
006D000000yLy3MIAS	03/04/2018 22:48	184300
006D000000yLy3MIAS	03/04/2018 22:49	184525
006D000000yLy3MIAS	03/04/2018 22:50	184615
006D000000yLy3MIAS	03/04/2018 22:52	184770

I would like to have a measure that would find the last added amount by CreatedDate and by ID with the final output for the above example being: 99257 + 116110 + 115000 + 80000 + 113000 + 117300 + 115000 + 184770

I have created a measure to extract the last value but I'm missing the sum of values by ID (if it helps):

LastAmount = CALCULATE(LASTNONBLANK('Table'[Amount__hst], ""), FILTER('Table', MAX('Table'[CreatedDate])))

Any help?

Best regards,

Antonio

[Greg_Deckler · Accepted solution]

So, you should be able to do something like:

Measure = 
VAR __tmpTable = SUMMARIZE('Table',[Id],"__LastAmount",[LastAmount])
RETURN SUMX(__tmpTable,[__LastAmount])

[antoniopgouveia]

Thanks! That works perfectly! 

Is there any chance I can do the same with one measure only?

[Greg_Deckler]

You could always do something like this:

Measure = 
VAR __myLastAmount = CALCULATE(LASTNONBLANK('Table'[Amount__hst], ""), FILTER('Table', MAX('Table'[CreatedDate])))
VAR __tmpTable = SUMMARIZE('Table',[Id],"__LastAmount",__myLastAmount)
RETURN SUMX(__tmpTable,[__LastAmount])

[wesen]

@Greg_Deckler 

Is it possible to apply the same scenario on direct query mode specially on the power bi report server ??