cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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 III
Super User III

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] )
)
Try my new Power BI game Cross the River

View solution in original post

3 REPLIES 3
Super User III
Super User III

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] )
)
Try my new Power BI game Cross the River

View solution in original post

Super User III
Super User III

Re: Calculate value by last date and grouped by ID

@antoniopgouveia

 

See attached file as well

 

antonio.png

Try my new Power BI game Cross the River
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
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors