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

 

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

 

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
Super User IV

So, you should be able to do something like:

 

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

 


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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

4 REPLIES 4
Super User IV
Super User IV

So, you should be able to do something like:

 

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

 


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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Thanks! That works perfectly! 

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

 

 

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])

 


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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@Greg_Deckler 

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

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