Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
antoniopgouveia
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
Greg_Deckler
Super User
Super User

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
Super User

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.