Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
78 | |
75 | |
50 |
User | Count |
---|---|
144 | |
109 | |
108 | |
87 | |
61 |