Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
Antonio,
Try adding this MEASURE
Measure = IF ( HASONEFILTER ( 'Table'[ParentId ] ), [LastAmount], SUMX ( ALLSELECTED ( 'Table'[ParentId ] ), [LastAmount] ) )
Antonio,
Try adding this MEASURE
Measure = IF ( HASONEFILTER ( 'Table'[ParentId ] ), [LastAmount], SUMX ( ALLSELECTED ( 'Table'[ParentId ] ), [LastAmount] ) )
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.
See attached file as well
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |