Calculating sum of unique historical values

[shad0wca7]

I have a number of deals in a table which are historical values - all changes to the deal stage are recorded but what I want to have is a sum of the historical amounts for a unique deal ID (e.g. not just the sum of all changes for a particular deal ID.. but the sum of all deals in a time period using the most relevant date and unique ID)

dealid	Stage	Date	Deal value
1	Open	01/04/2013	$         1.00
1	Evaluating	10/04/2013	$         2.00
1	Negotiation	12/04/2013	$         3.00
2	Open	10/04/2013	$         1.00
2	Evaluating	11/04/2013	$         2.00
2	Closed	15/04/2013	$         1.00
1	Won	16/04/2013	$         3.00

I want to display this per quarter e.g. I want to know what the sum is of all deals that I've been dealing with on a particular quarter and then be able to filter by stage. This is because the values of these deals change depending on their pipeline stages often (or as more is known about the deal)...

I'm struggling to find an elegant way to do this and making myself confused.. Any help much appreciated

[v-jayw-msft · Accepted solution]

Hi @shad0wca7 ,

Not quite understand what you want, are you want the sum of value per id and per quarter? If so please refer to the measure below.

Measure = CALCULATE(SUM('Table'[Deal value]),FILTER(ALLEXCEPT('Table','Table'[dealid]),FORMAT('Table'[Date],"q")=FORMAT(SELECTEDVALUE('Table'[Date]),"q")))

 

If i misunderstood your meaning, please let me know.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.