cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## Calculating sum of unique historical values

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

1 ACCEPTED SOLUTION
Community Support

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")))``

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.

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
Community Support

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")))``

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.

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors