Forum Discussion

itchyeyeballs's avatar
itchyeyeballs
Impactful Individual
7 years ago
Solved

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

 

Need Help
  • jmalone's avatar
    jmalone
    7 years ago

    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

  • jmalone's avatar
    jmalone
    Resolver III
    7 years ago

    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] )
    )
)
    • itchyeyeballs's avatar
      itchyeyeballs
      Impactful Individual
      7 years ago

      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

      • v-frfei-msft's avatar
        v-frfei-msft
        Community Support
        7 years ago

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