Reply
Nels921
Frequent Visitor

Creating a summary table to show recent type of emails sent

I've have a data set that I've been really struggling to summarize and create a report that is easy to understand. I basically have two tables, one is Customer which has all the customer details and the other is the "Activities" which stores the activities done on their account (i.e notes on their account, emails and reminders that have been sent). They're linked on the Customer ID field.  I'm working on a query to identify if the system has sent them two reminders and the date that the most recent reminders were sent. 

 

Below is an example of the data. I added the two custom columns "60 Day Sent" and "30 Day Sent" to narrow down only the reminder emails sent by the system. 

 

Customer IDSubject Date Sent 60 Day Sent30 Day Sent
C24333460-day notice to renew your course: C2433342022-03-05TRUEFALSE
C243334Course will expire in 30 days: C2433342022-04-05FALSETRUE
C24333460-day notice to renew your course: C2433342021-03-05TRUEFALSE
C243334Course will expire in 30 days: C2433342021-04-05FALSETRUE
C8909860-day notice to renew your course: C890982022-09-04TRUEFALSE
C345460-day notice to renew your course: C34542022-08-03TRUEFALSE
C3454Course will expire in 30 days C34542022-09-03FALSETRUE
C775592Course will expire in 30 days C7755922022-07-15FALSETRUE
C0934860-day notice to renew your course: C093482021-06-14TRUEFALSE
C0934860-day notice to renew your course: C093482022-06-14TRUEFALSE

 

Where I'm stuck is creating a table or report that summarises if we've sent reminders and the date of the most recent reminder of each type. For example for customers C243334 and C09348, I only want to see the reminders we sent them in 2022 since those are the most recent. If we haven't sent anything the value can either be No or Blank.   This would be my desired result:

 

Customer ID60 Day ReminderDate Sent 30 Day ReminderDate Sent 
C243334Yes2022-03-05Yes2022-04-05
C89098Yes2022-09-04  
C3454Yes2022-08-03Yes2022-09-03
C775592  Yes2022-07-15
C09348Yes2022-06-14  
1 ACCEPTED SOLUTION
daXtreme
Solution Sage
Solution Sage

1 REPLY 1
daXtreme
Solution Sage
Solution Sage

File attached.

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)