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
AliceW
Impactful Individual
Impactful Individual

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

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 IDChange DateModified By
101-Feb-2020John
115-Feb-2020Adam

 

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
1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

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

 

View solution in original post

7 REPLIES 7
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
camargos88
Community Champion
Community Champion

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



AliceW
Impactful Individual
Impactful Individual

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

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



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



amitchandak
Super User
Super User

@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
Impactful Individual
Impactful Individual

@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
Impactful Individual
Impactful Individual

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

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.