Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
So, you should be able to do something like:
Measure = VAR __tmpTable = SUMMARIZE('Table',[Id],"__LastAmount",[LastAmount]) RETURN SUMX(__tmpTable,[__LastAmount])
So, you should be able to do something like:
Measure = VAR __tmpTable = SUMMARIZE('Table',[Id],"__LastAmount",[LastAmount]) RETURN SUMX(__tmpTable,[__LastAmount])
Thanks! That works perfectly!
Is there any chance I can do the same with one measure only?
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])
Is it possible to apply the same scenario on direct query mode specially on the power bi report server ??
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |