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'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
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 |
---|---|
107 | |
98 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |