cancel
Showing results for
Did you mean:
Frequent Visitor

## Calculate last value by date and id

Hi all,

I have a list of opportunities where all changes to them are recorded. I want to be able to extract the last sales amount for a certain time period and for each opportunity, the issue being that there can multiple changes to the same opportunity. Below, I have a sample with the Opportunity ID, Creation Date and Sales amount:

 Id 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: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

I would like to have a measure that would find the last added amount by CreatedDate and by ID with the final output for the above example being: 99257 + 116110 + 115000 + 80000 + 113000 + 117300 + 115000 + 184770

I have created a measure to extract the last value but I'm missing the sum of values by ID (if it helps):

`LastAmount = CALCULATE(LASTNONBLANK('Table'[Amount__hst], ""), FILTER('Table', MAX('Table'[CreatedDate])))`

Any help?

Best regards,

Antonio

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV

So, you should be able to do something like:

```Measure =
VAR __tmpTable = SUMMARIZE('Table',[Id],"__LastAmount",[LastAmount])
RETURN SUMX(__tmpTable,[__LastAmount])```

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

4 REPLIES 4
Super User IV

So, you should be able to do something like:

```Measure =
VAR __tmpTable = SUMMARIZE('Table',[Id],"__LastAmount",[LastAmount])
RETURN SUMX(__tmpTable,[__LastAmount])```

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Frequent Visitor

Thanks! That works perfectly!

Is there any chance I can do the same with one measure only?

Super User IV

You could always do something like this:

```Measure =
VAR __myLastAmount = CALCULATE(LASTNONBLANK('Table'[Amount__hst], ""), FILTER('Table', MAX('Table'[CreatedDate])))
VAR __tmpTable = SUMMARIZE('Table',[Id],"__LastAmount",__myLastAmount)
RETURN SUMX(__tmpTable,[__LastAmount])```

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Helper I

Is it possible to apply the same scenario on direct query mode specially on the power bi report server ??

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors