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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
itchyeyeballs
Impactful Individual
Impactful Individual

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

 

EXCEL_XWGHQIW2yL.png

1 ACCEPTED SOLUTION

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] )
    )
)

View solution in original post

5 REPLIES 5
jmalone
Resolver III
Resolver III

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] )
    )
)

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

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] )
    )
)

Hi @itchyeyeballs ,

 

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.

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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