cancel
Showing results for
Did you mean:
Highlighted
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

 006D000000pOj4XIAS 03/04/2018 09:46 99257 006D000000rHnhtIAC 03/04/2018 23:57 116110 006D000000uHe3xIAC 03/04/2018 21:10 115000 006D000000xjhG7IAI 03/04/2018 11:50 80000 006D000000yLtscIAC 02/04/2018 20:11 110000 006D000000yLtscIAC 02/04/2018 20:56 113000 006D000000yLtscIAC 02/04/2018 20:56 113000 006D000000yLvrnIAC 03/04/2018 09:38 1 006D000000yLvrnIAC 03/04/2018 09:39 117300 006D000000yLy1uIAC 03/04/2018 22:30 006D000000yLy1uIAC 03/04/2018 22:34 115000 006D000000yLy1uIAC 03/04/2018 22:54 115000 006D000000yLy3MIAS 03/04/2018 22:38 100000 006D000000yLy3MIAS 03/04/2018 22:40 170100 006D000000yLy3MIAS 03/04/2018 22:43 175300 006D000000yLy3MIAS 03/04/2018 22:48 184300 006D000000yLy3MIAS 03/04/2018 22:49 184525 006D000000yLy3MIAS 03/04/2018 22:50 184615 006D000000yLy3MIAS 03/04/2018 22:52 184770

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

## Re: Calculate value by last date and grouped by ID

@antoniopgouveia

Antonio,

```Measure =
IF (
HASONEFILTER ( 'Table'[ParentId ] ),
[LastAmount],
SUMX ( ALLSELECTED ( 'Table'[ParentId ] ), [LastAmount] )
)```
Try my new Power BI game Cross the River
3 REPLIES 3
Super User III

## Re: Calculate value by last date and grouped by ID

@antoniopgouveia

Antonio,

```Measure =
IF (
HASONEFILTER ( 'Table'[ParentId ] ),
[LastAmount],
SUMX ( ALLSELECTED ( 'Table'[ParentId ] ), [LastAmount] )
)```
Try my new Power BI game Cross the River
Super User III

## Re: Calculate value by last date and grouped by ID

@antoniopgouveia

See attached file as well

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

Announcements

#### 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!

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?

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

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