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

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

New Member

## Sum w/o sum doublicates

Dear all

how can I sum following situation correctly:

From the table below: I have 2x Status "paid" but i only want to sum "amount" 1x with the smaller date from "modified_on".

So when I sum (amount) it should only count the 80 at modified_on 28.01.2022.

Thank you all for your help!!!

 id rev revtype created_on modified_on address amount date license_plate number payment_period status legal_status 4321 55 1 22.11.2021 10:33 15.12.2021 17:59 Test 80 15.12.2021 17:59 ZH123 016742516 21 ISSUED NONE 4321 133 1 22.11.2021 10:33 07.01.2022 03:00 Test 80 15.12.2021 17:59 ZH123 016742516 21 OVERDUE NONE 4321 199 1 22.11.2021 10:33 28.01.2022 07:36 Test 80 15.12.2021 17:59 ZH123 016742516 21 PAID NONE 4321 393 1 22.11.2021 10:33 01.03.2022 18:53 Test 80 15.12.2021 17:59 ZH123 016742516 21 PAID REMINDED
1 ACCEPTED SOLUTION
Community Support

Hi @mybeppo ,

Please try the following formula:

``````Measure =
VAR mindate =
MINX (
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[id] = MAX ( 'Table'[id] )
&& 'Table'[status] = "PAID"
),
'Table'[modified_on]
)
RETURN
CALCULATE (
SUM ( 'Table'[amount] ),
FILTER ( 'Table', 'Table'[modified_on] = mindate && 'Table'[status] = "PAID" )
)``````

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 REPLIES 4
Community Support

Hi @mybeppo ,

Please try the following formula:

``````Measure =
VAR mindate =
MINX (
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[id] = MAX ( 'Table'[id] )
&& 'Table'[status] = "PAID"
),
'Table'[modified_on]
)
RETURN
CALCULATE (
SUM ( 'Table'[amount] ),
FILTER ( 'Table', 'Table'[modified_on] = mindate && 'Table'[status] = "PAID" )
)``````

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Super User

@mybeppo Try this measure:

Measure = CALCULATE(SUM(StatusTable[amount]),FILTER(StatusTable,StatusTable[modified_on]<=MIN(StatusTable[modified_on])),StatusTable[status]="PAID")

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard
Memorable Member

Hi @mybeppo , Will something like this work?

Measure =
Var MinDate_ = CALCULATE( MIN('Table'[modified_on]),ALLEXCEPT('Table','Table'[status])) RETURN
CALCULATE(SUM('Table'[amount]),FILTER('Table','Table'[status] = "PAID" &&'Table'[modified_on]=MinDate_))
New Member

thank you, unfortunatly it shows no results

## Helpful resources

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