cancel
Showing results for
Did you mean:
Highlighted
Established Member

## Sum earliest value based on row context

Hi all,

looking for some help on how to dynamically sum the earliest value for a record based on the row context.

I basically want to be able to find and sum up the earliest record for each value based on the row context, the image below shows some sample data and the different outputs I'd like to achieve. The example only shows one record ID but where there are multiple I need to sum them all together for each scenario.

I've not had much luck searching for a solution so far so hoping you guys can help me out. I'd like to use the same DAX expression across a range of tables and visuals if possible

1 ACCEPTED SOLUTION

Accepted Solutions
Member

## Re: Sum earliest value based on row context

Sorry, I missed the part about needing the single record number. I modified my original measure to add a filter condition for the record id.

I'm not sure why you'd want to use the SUMX in addition to the filter condition within CALCULATE. Usually, the pattern would be either:
SUMX ( FILTER( table, conditions), expression)
or
CALCULATE ( expression , filter conditions)

but it's unlikely you'd need both.

```Sum of Total  at Earliest Date =
CALCULATE (
SUM ( 'Table'[Total] ),
FILTER (
'Table',
'Table'[Date]  = MIN ( 'Table'[Date] )
&& 'Table'[record_id]  = MIN ( 'Table'[record_id] )
)
)```
5 REPLIES 5
Member

## Re: Sum earliest value based on row context

You'll want to write a measure with the following pattern. The trick is to use FILTER() to perform your sum on the records with the MIN date. The rest of the row context (i.e. RecordID, group1, group2, etc.) is dynamic based on your visual/chart.

```Sum of Total  at Earliest Date =
CALCULATE (
SUM ( 'Table'[Total] ),
FILTER (
'Table',
'Table'[Date] = MIN ( 'Table'[Date] )
)
)```
Established Member

## Re: Sum earliest value based on row context

Hi,

Thank you for the response, I'm having trouble to get it to work though.

I need to get the minimum value for each record_id based on the row context and sum them, the example I gave only had a single record_id but the actual table has many.

At the moment the numbers being returned are too low.

Edit - I seem to be getting closer by changing to

```Sum of Total  at Earliest Date =
CALCULATE (
SUMX (allselected('Table'[record_id],sum ( 'Table'[Total] )),
FILTER (
'Table',
'Table'[Date] = MIN ( 'Table'[Date] )
)
)```

but my still not adding up correctly

Community Support Team

## Re: Sum earliest value based on row context

Try to update your measure as below.

```Sum of Total  at Earliest Date =
CALCULATE (
SUMX (allselected('Table'[record_id],sum ( 'Table'[Total] )),
FILTER (
ALLSELECTED('Table'),
'Table'[Date] = MIN ( 'Table'[Date] )
)
)```
Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Established Member

## Re: Sum earliest value based on row context

Hi,

Still not working 😞

I'm not sure the formula in its current form is picking up the earliest record for each row ID, it seems to be finding the earliest record for the group.

Each record_id can appear across multiple groups but only its first instance in that group should be counted (hope that makes sense)

Edit:

I have tried switching things around to look like:

```=SUMX (
VALUES( 'Table'[record_id] ),
CALCULATE (
sum ( 'Table'[Total] ),
FILTER (
'Table',
'Table'[Date]
= [date_min]
)
)
)

date_min = calculate([min('Table'[Date]),'Table'[Total]>0)```

but my totals and subtotals are way too high, I'm guessing I'm losing the row context somewhere but not sure how to fix

Member

## Re: Sum earliest value based on row context

Sorry, I missed the part about needing the single record number. I modified my original measure to add a filter condition for the record id.

I'm not sure why you'd want to use the SUMX in addition to the filter condition within CALCULATE. Usually, the pattern would be either:
SUMX ( FILTER( table, conditions), expression)
or
CALCULATE ( expression , filter conditions)

but it's unlikely you'd need both.

```Sum of Total  at Earliest Date =
CALCULATE (
SUM ( 'Table'[Total] ),
FILTER (
'Table',
'Table'[Date]  = MIN ( 'Table'[Date] )
&& 'Table'[record_id]  = MIN ( 'Table'[record_id] )
)
)```

Announcements