Calculate the 'Latest Modified By' based on 'Latest Change Date'

[AliceW]

Hi guys,

I have the feeling this is simple, yet I can't figure it out.

So, I have this table CHANGES (simplified, lol):

Opportunity ID	Change Date	Modified By
1	01-Feb-2020	John
1	15-Feb-2020	Adam

I need:

- the latest Change Date per Opportunity ID;

- who did it (so, 'Modified By' column).

I got, via this measure, the latest Change Date.

The Latest Change Date =

MAXX(

    KEEPFILTERS(VALUES('CHANGES'[Opportunity ID])),

    CALCULATE(MAX('CHANGES'[Change Date]))

)

However, I miss (in a column or a measure), the Modified By name.

Could you help me out, please?

Thank you,

Alice

[Ashish_Mathur]

Hi,

I blogged about my solution to a very question yesterday - Show text entries in the value area section of a Pivot Table after meeting certain conditions.

Hope this helps.

[camargos88]

Hi @AliceW ,

You can create these 2 measures:

Max_Date = CALCULATE(MAX('Table'[Change Date]); ALLEXCEPT('Table'; 'Table'[Opportunity ID]))

Modified By LD = CALCULATE(DISTINCT('Table'[Modified By]); FILTER(ALLEXCEPT('Table';'Table'[Opportunity ID]); 'Table'[Change Date] = [Max_Date]))

Ricardo

[AliceW]

Hi Camargos,

First of all, thank you for replying! The first formula is great, but I get an error for the second: 'a table of multiple values was supplied where a single value was expected'.

Do you know what happened?

Thank yoU!

Ioana

[camargos88]

Hi @AliceW ,

It usually happens when you have all set combined set more than 1x, once DISTINCT function hopes only 1 return, if you have more than it, you get an error like u got.

Ricardo

[amitchandak · Accepted solution]

@AliceW ,

For date you can take max(Table[Date])

and for modified by

Modified By Name = 
VAR __id = MAX ( 'Table'[Opportunity ID] )
VAR __Change Date = CALCULATE ( MAX( 'Table'[Change Date] ), ALLSELECTED ( 'Table' ),  'Table'[Opportunity ID] = __id ) 
RETURN CALCULATE ( MAX ( 'Table'[Modified By] ), VALUES ( 'Table'[Opportunity ID] ), 'Table'[Opportunity ID] = __id, 'Table'[Change Date] = __Change Date )

[AliceW]

@amitchandakone follow-up question, if I may. I'd like to have this beautiful measure as a column in another table, which has one Opportunity ID per line.

This way, I can use it in visuals as Axis. Super important! Thank you so much.

[AliceW]

Thank you so much, @amitchandak !! It worked!!!