Forum Discussion

cdawidow2's avatar
cdawidow2
Frequent Visitor
4 years ago

Last Value from a table?

Hi guys, I want to build a dax measure that essentially allows me to calculuate the Stage Name & Amount of a sales opportunity in my pipeline historically.   I have both CreatedDate and systemmodstam...
DAX
Help Requested
POWER BI Dax rules
cdawidow2's avatar
cdawidow2
Frequent Visitor
4 years ago

Hi Amit,

 

I cannot share my file but perhaps I can make it much clearer for you.  Below is an example from my file.  

Essentially, I set my date filter so be sometime in the past.  Now if I select an Opportunity, ( Academic Renewal), below I get their respective stage history.  My measures I built out correctly retrieve the last value for both its historic stage and amount which was on March 3rd 2021 as it is the lastnonblank date within the date filters applied.  My measure fpr historical amount is below.  

 

HISTORICAL AMOUNT = VAR X = calculate(MAXX(VALUES('Opportunity History'[SystemModstamp]),'Opportunity History'[SystemModstamp]))
RETURN


CALCULATE(FIRSTNONBLANK('Opportunity History'[Amount],'Opportunity History'[Amount]),'Opportunity History'[SystemModstamp]=X)


 

My issue is how do I correctly sum each respect opportunity lastnonblank historical amount when these date filters are applied?  As of now I am not getting the correct result as shown below .  The total value is super low.  My question is how do i essentially sum up all these lastnonblank values up for a correct total amount?