Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
antoniopgouveia
Frequent Visitor

Fill in gaps with previous values from join table

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:

 

ParentIdCurrencyIsoCodeCreatedDateAmount__hprAmount__hst
006D000000zPBOfIAOGBP06/08/2018 08:49 80000
006D000000zP8qRIASUSD03/08/2018 16:09 85000
006D000000zP8qRIASUSD03/08/2018 16:098500098800
006D000000zP7ZCIA0GBP03/08/2018 10:35 1
006D000000zP5kIIASUSD02/08/2018 22:14 1000
006D000000zP5kIIASUSD02/08/2018 22:1510001225
006D000000zP5H6IAKCAD02/08/2018 16:01 200000
006D000000zP5H6IAKCAD02/08/2018 16:02200000157240
006D000000zP5arIACUSD02/08/2018 20:28 125000
006D000000zP5arIACUSD02/08/2018 20:29125000117500
006D000000zP4cMIASGBP02/08/2018 13:37 85000
006D000000zP3a0IACGBP02/08/2018 09:04 70000
006D000000zP1qiIACUSD01/08/2018 20:19 152000
006D000000zP1qiIACUSD01/08/2018 20:19152000139200
006D000000zP1qiIACUSD01/08/2018 20:20139200238000
006D000000zOyKyIAKUSD01/08/2018 00:01 125000
006D000000zOyKyIAKUSD01/08/2018 00:02125000117500
006D000000zOyGJIA0USD31/07/2018 23:48 75000
006D000000zOyGJIA0USD31/07/2018 23:497500078100
006D000000zOxN6IAKUSD31/07/2018 21:33 100000
006D000000zOxN6IAKUSD31/07/2018 21:3410000098800
006D000000zOwexIACGBP31/07/2018 15:00 80000
006D000000zOwexIACGBP31/07/2018 15:018000087700
006D000000zOwexIACGBP01/08/2018 08:168770088305
006D000000zOwexIACGBP01/08/2018 08:388830588305
006D000000zOwdpIACEUR31/07/2018 14:56 1
006D000000zOwdpIACEUR31/07/2018 14:5812550

 

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:

2018-08-16 (2).png

 

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

1 REPLY 1
v-chuncz-msft
Community Support
Community Support

@antoniopgouveia,

 

You may try using TOPN Function.

https://community.powerbi.com/t5/Desktop/Find-last-value-based-on-date/m-p/293150#M129228

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.