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.

Reply
mkeiper
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:

IDAmount lostAs of Date
11001/1/2010
22001/1/2010
33001/1/2010
44001/1/2010
11501/1/2011
22501/1/2011
33501/1/2011
42001/1/2011
55001/1/2011
66001/1/2011
11501/1/2012
23001/1/2012
34001/1/2012
42501/1/2012
54501/1/2012
68001/1/2012
77001/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
Sean
Community Champion
Community Champion

@mkeiper

Hope this works and helps! Smiley Happy

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

Sum of records filtered by earliest date - 2017-10-06.png

Good Luck! Smiley Happy

View solution in original post

2 REPLIES 2
Sean
Community Champion
Community Champion

@mkeiper

Hope this works and helps! Smiley Happy

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

Sum of records filtered by earliest date - 2017-10-06.png

Good Luck! Smiley Happy

mkeiper
Frequent Visitor

You sir are a genius.

 

Thanks

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.