Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all,
I'm trying to recreate the steps of an opportunity pipeline by looking at the value of opportunities at different months. I'm using a table data contains all the history for each opportunity and that contains all amount values that each opportunity assumed since its creation. It looks like this:
ParentId | CurrencyIsoCode | CreatedDate | Amount__hpr | Amount__hst |
006D000000zPBOfIAO | GBP | 06/08/2018 08:49 | 80000 | |
006D000000zP8qRIAS | USD | 03/08/2018 16:09 | 85000 | |
006D000000zP8qRIAS | USD | 03/08/2018 16:09 | 85000 | 98800 |
006D000000zP7ZCIA0 | GBP | 03/08/2018 10:35 | 1 | |
006D000000zP5kIIAS | USD | 02/08/2018 22:14 | 1000 | |
006D000000zP5kIIAS | USD | 02/08/2018 22:15 | 1000 | 1225 |
006D000000zP5H6IAK | CAD | 02/08/2018 16:01 | 200000 | |
006D000000zP5H6IAK | CAD | 02/08/2018 16:02 | 200000 | 157240 |
006D000000zP5arIAC | USD | 02/08/2018 20:28 | 125000 | |
006D000000zP5arIAC | USD | 02/08/2018 20:29 | 125000 | 117500 |
006D000000zP4cMIAS | GBP | 02/08/2018 13:37 | 85000 | |
006D000000zP3a0IAC | GBP | 02/08/2018 09:04 | 70000 | |
006D000000zP1qiIAC | USD | 01/08/2018 20:19 | 152000 | |
006D000000zP1qiIAC | USD | 01/08/2018 20:19 | 152000 | 139200 |
006D000000zP1qiIAC | USD | 01/08/2018 20:20 | 139200 | 238000 |
006D000000zOyKyIAK | USD | 01/08/2018 00:01 | 125000 | |
006D000000zOyKyIAK | USD | 01/08/2018 00:02 | 125000 | 117500 |
006D000000zOyGJIA0 | USD | 31/07/2018 23:48 | 75000 | |
006D000000zOyGJIA0 | USD | 31/07/2018 23:49 | 75000 | 78100 |
006D000000zOxN6IAK | USD | 31/07/2018 21:33 | 100000 | |
006D000000zOxN6IAK | USD | 31/07/2018 21:34 | 100000 | 98800 |
006D000000zOwexIAC | GBP | 31/07/2018 15:00 | 80000 | |
006D000000zOwexIAC | GBP | 31/07/2018 15:01 | 80000 | 87700 |
006D000000zOwexIAC | GBP | 01/08/2018 08:16 | 87700 | 88305 |
006D000000zOwexIAC | GBP | 01/08/2018 08:38 | 88305 | 88305 |
006D000000zOwdpIAC | EUR | 31/07/2018 14:56 | 1 | |
006D000000zOwdpIAC | EUR | 31/07/2018 14:58 | 1 | 2550 |
The ParentID is the field that identifies each opportunity - Primary key. Everytime there is an update to an opportunity, a entry is created that contais the new amount value - Amount_hst - and also contains what the previous value was - Amount_hpr. If Amount_hpr is blank it means there was no previous value since its creation.
There can be several updates to each opportunity per month so I have built the following measures to get the sum of amount for the most recent update to each opportunity:
LastAmount = CALCULATE(VALUES('Table'[Amount__hst]), filter('Table', 'Table'[CreatedDate]=max('Table'[CreatedDate])))
and
SumLastAmount = SUMX ( ALLSELECTED( 'Table'[ParentId]), [LastAmount] )
Then, I'm combining a calendar table, the matching opportunity ID from my opportunity table and the SumLastAmount measure to produce the following table:
Due to the fact that there will only be a value entry when there is a change to an opportunity I will have gaps on the months where there was no update, meaning that the correct value is still the one from the previous update.
Now the problem, is that I would like to have all cells filled up with the most recent values - previous value - for each of the months and opportunity in the above table. Any idea on how to do that in a measure or maybe changing my exisitng measures to make it happen?
Best regards,
Antonio
You may try using TOPN Function.
https://community.powerbi.com/t5/Desktop/Find-last-value-based-on-date/m-p/293150#M129228
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |