Calculate value by last date and grouped by ID

[antoniopgouveia]

Hi all,

I have a table that records all the changes made to a list of opportunities. These changes can affect the same opportunity at different time points. For example, if an opportunity is created and a certain sales amount is input and later changed to a new value, both modifications would be listed in the table. I have the following sample:

ParentId                                                                               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:30
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

As you can see some of the changes in amount affect the same opportunity (same ID) and I would like to be able to have a measure that would find the last inserted value (by creation date) and by opportunity ID. The goal is to track changes on opportunities so when I sum all the last values for each opportunity, I can compare it for different time intervals. 

I have come up with the following measure that returns the last input value:

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

However, it does not sum the different last values for the different opportunities. Any help?

For this example the value I would be looking for would be: 99257 + 116110 + 115000 + 80000 + 113000 + 117300 + 115000 + 184770

Best regards,

Antonio

[Zubair_Muhammad · Accepted solution]

@antoniopgouveia

Antonio,

Try adding this MEASURE

Measure =
IF (
    HASONEFILTER ( 'Table'[ParentId ] ),
    [LastAmount],
    SUMX ( ALLSELECTED ( 'Table'[ParentId ] ), [LastAmount] )
)

[antoniopgouveia]

Thanks! That works perfectly!

Is there any chance of getting the same result with one measure only? I have tried to replace the LastAmount by the actual expression in the formula you have posted but the result I'm getting is different. 

[Zubair_Muhammad]

@antoniopgouveia

See attached file as well