cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
antoniopgouveia Frequent Visitor
Frequent Visitor

Calculate value by last date and grouped by ID

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     

006D000000pOj4XIAS03/04/2018 09:4699257
006D000000rHnhtIAC03/04/2018 23:57116110
006D000000uHe3xIAC03/04/2018 21:10115000
006D000000xjhG7IAI03/04/2018 11:5080000
006D000000yLtscIAC02/04/2018 20:11110000
006D000000yLtscIAC02/04/2018 20:56113000
006D000000yLtscIAC02/04/2018 20:56113000
006D000000yLvrnIAC03/04/2018 09:381
006D000000yLvrnIAC03/04/2018 09:39117300
006D000000yLy1uIAC03/04/2018 22:30 
006D000000yLy1uIAC03/04/2018 22:34115000
006D000000yLy1uIAC03/04/2018 22:54115000
006D000000yLy3MIAS03/04/2018 22:38100000
006D000000yLy3MIAS03/04/2018 22:40170100
006D000000yLy3MIAS03/04/2018 22:43175300
006D000000yLy3MIAS03/04/2018 22:48184300
006D000000yLy3MIAS03/04/2018 22:49184525
006D000000yLy3MIAS03/04/2018 22:50184615
006D000000yLy3MIAS03/04/2018 22:52184770

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Calculate value by last date and grouped by ID

@antoniopgouveia

 

Antonio,

 

Try adding this MEASURE

 

Measure =
IF (
    HASONEFILTER ( 'Table'[ParentId ] ),
    [LastAmount],
    SUMX ( ALLSELECTED ( 'Table'[ParentId ] ), [LastAmount] )
)

View solution in original post

3 REPLIES 3
Super User
Super User

Re: Calculate value by last date and grouped by ID

@antoniopgouveia

 

Antonio,

 

Try adding this MEASURE

 

Measure =
IF (
    HASONEFILTER ( 'Table'[ParentId ] ),
    [LastAmount],
    SUMX ( ALLSELECTED ( 'Table'[ParentId ] ), [LastAmount] )
)

View solution in original post

Super User
Super User

Re: Calculate value by last date and grouped by ID

@antoniopgouveia

 

See attached file as well

 

antonio.png

antoniopgouveia Frequent Visitor
Frequent Visitor

Re: Calculate value by last date and grouped by ID

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. 

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 179 members 2,487 guests
Please welcome our newest community members: