cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Frequent Visitor

## Sum of records filtered by earliest date

I am trying to find a sum by filtering by earliest date on each row. I have multiple records with the same ID number. Amount lost changes over time and my data keeps record of these changes with.

Here is an example of how my data is set up:

 ID Amount lost As of Date 1 100 1/1/2010 2 200 1/1/2010 3 300 1/1/2010 4 400 1/1/2010 1 150 1/1/2011 2 250 1/1/2011 3 350 1/1/2011 4 200 1/1/2011 5 500 1/1/2011 6 600 1/1/2011 1 150 1/1/2012 2 300 1/1/2012 3 400 1/1/2012 4 250 1/1/2012 5 450 1/1/2012 6 800 1/1/2012 7 700 1/1/2012

What I am trying to execute is taking the sum of the Amount Lost by Earliest As of Date of each ID. For this data it would be summing the Bolded records. My closest attempt is this:

Earliest Lost = CALCULATE(sum(LD[Amount Lost]), FILTER(LD, LD[As of Date]=MINX(values(LD[As of Date]), LD[As of Date]))).

This works for each individual record, pulling the earliest Amount Lost, but the overall sum only adds the 1/1/2010 as of dates and excludes all others.

Thanks in advance for the help

1 ACCEPTED SOLUTION
Community Champion

@mkeiper

Hope this works and helps!

```Measure =
CALCULATE (
SUM ( LD[Amount lost] ),
FILTER (
LD,
LD[As of Date] = CALCULATE ( MIN ( LD[As of Date] ), ALLEXCEPT ( LD, LD[ID] ) )
)
)```

Good Luck!

2 REPLIES 2
Community Champion

@mkeiper

Hope this works and helps!

```Measure =
CALCULATE (
SUM ( LD[Amount lost] ),
FILTER (
LD,
LD[As of Date] = CALCULATE ( MIN ( LD[As of Date] ), ALLEXCEPT ( LD, LD[ID] ) )
)
)```

Good Luck!

Frequent Visitor

You sir are a genius.

Thanks

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors