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
rocky09
Solution Sage
Solution Sage

Get Latest Contacted information from a table

I have this following table. I want to get latest contacted Date and User Details.

DateUserNameTypeFollowup
24-04-2016 2:35 AMMike1294EmailYes
24-04-2016 1:45 AMAnand8372CallYes
24-04-2016 2:15 PMKristy9382EmailYes
 Anand8372  
25-04-2016 2:25 AMMike1294EmailYes
 Anand8372  

 

I want to achieve results like below.

 

DateUserNameType
24-04-2016Kristy9382Email
25-04-2016Mike1294Email

 

Thank you inadvance.

3 REPLIES 3
Ashish_Mathur
Super User
Super User

Hi,

 

Try this

 

  1. Drag Username to the row labels
  2. Enter the following calculated field formula to get the last contact date - [Last contact date]

 

=MAX(Data[Date])

 

  1. Enter the following calculated field formula to get the type on the last contact date
=LOOKUPVALUE(Data[Type],Data[Date],[Last contact date])

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-jiascu-msft
Employee
Employee

Hi @rocky09,

 

Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.

 

Best Regards!
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
TomMartens
Super User
Super User

Hey,

 

I created two calculated columns in my table,

the first column just retrieves the date part of the my datetime column like so

JustDate = 
    DATE(YEAR('Table1'[ContactDateTime]),MONTH('Table1'[ContactDateTime]),DAY('Table1'[ContactDateTime])) 

the 2nd column marks the row with the latest DateTime for each day with TRUE otherwise FALSE like so

Is Latest Contact = 
IF(
    AND('Table1'[ContactDateTime] = 
        CALCULATE(
            MAX('Table1'[ContactDateTime])
            ,ALLEXCEPT('Table1',Table1[JustDate])
        )
        ,FORMAT('Table1'[ContactDateTime],"YYYY-MM-DD") <> ""
    )
    ,"TRUE"
    ,"FALSE"
)

My table finally looks like this

2017-09-24_8-31-27.png

 

I would hide my JustDate column in report view and use the column Is Latest Contact to filter the rows accordingly.

 

Hope this helps

 

Regards

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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.