Forum Discussion

antlufc's avatar
antlufc
Frequent Visitor
3 years ago

Value Based on Max Date from Another Column

Hi, 

I am looking to find a DAX measure/measures that will let me find the value of a sales lead based upon the max date time only if there is more than one entry in the same month. I do not want to find the value of the max date time associated with each lead as i am trending them based upon date as to when closed dates have been moved forward or backwards. I have attached a sample excel file with data and column names as per my PBI report.  I have highlighted in yellow those that i would expect to see as the single value. https://1drv.ms/f/s!AivZWzcfJJzngTyP6aMWz0rJKWXU 

 

1 Reply

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Hi antlufc 
    Please refer to attached sample file with the proposed solution

    Measure = 
SUMX ( 
    SUMMARIZE ( 'Table', 'Table'[pipeline_journey_id], 'Table'[event_date] ),
    MAXX ( 
        INTERSECT ( 
            'Table',
            TOPN ( 
                1,
                CALCULATETABLE ( 
                    'Table', 
                    ALLEXCEPT ( 'Table', 'Table'[pipeline_journey_id],'Table'[event_date] ) 
                ),
                'Table'[event_datetime]
            )
        ),
        'Table'[amount_average_contract_value]
    )
)