- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 ID | Subject | Date Sent | 60 Day Sent | 30 Day Sent |
C243334 | 60-day notice to renew your course: C243334 | 2022-03-05 | TRUE | FALSE |
C243334 | Course will expire in 30 days: C243334 | 2022-04-05 | FALSE | TRUE |
C243334 | 60-day notice to renew your course: C243334 | 2021-03-05 | TRUE | FALSE |
C243334 | Course will expire in 30 days: C243334 | 2021-04-05 | FALSE | TRUE |
C89098 | 60-day notice to renew your course: C89098 | 2022-09-04 | TRUE | FALSE |
C3454 | 60-day notice to renew your course: C3454 | 2022-08-03 | TRUE | FALSE |
C3454 | Course will expire in 30 days C3454 | 2022-09-03 | FALSE | TRUE |
C775592 | Course will expire in 30 days C775592 | 2022-07-15 | FALSE | TRUE |
C09348 | 60-day notice to renew your course: C09348 | 2021-06-14 | TRUE | FALSE |
C09348 | 60-day notice to renew your course: C09348 | 2022-06-14 | TRUE | FALSE |
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 ID | 60 Day Reminder | Date Sent | 30 Day Reminder | Date Sent |
C243334 | Yes | 2022-03-05 | Yes | 2022-04-05 |
C89098 | Yes | 2022-09-04 | ||
C3454 | Yes | 2022-08-03 | Yes | 2022-09-03 |
C775592 | Yes | 2022-07-15 | ||
C09348 | Yes | 2022-06-14 |
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
File attached.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - February 2025
Check out the February 2025 Power BI update to learn about new features.

Subject | Author | Posted | |
---|---|---|---|
07-24-2023 06:58 AM | |||
07-18-2023 01:14 PM | |||
Anonymous
| 06-16-2020 02:31 AM | ||
05-27-2024 03:26 PM | |||
08-16-2024 12:23 AM |
User | Count |
---|---|
23 | |
12 | |
10 | |
10 | |
8 |
User | Count |
---|---|
16 | |
15 | |
15 | |
12 | |
10 |