Forum Discussion
Showing latest date before specific date
Thank you for sample data. Here is the code:
Customers with last ticket older than 2 weeks ago = var customer_last_ticket = MAXX( DISTINCT('Sample'[client_id]); MAX('Sample'[created_at]) ) return IF(DATEDIFF(customer_last_ticket;TODAY();DAY)>=14;customer_last_ticket)In the variable customer_last_ticket we are storing an information about last ticket per customer
Then if this ticket is older than 14 days ago we are displaying int 🙂
I hope that this is what you've wanted.
Test data is attached.
So for this example, the correct result would show client 171 and client 197 as follows (using - as table break line):
Client - Created_at
171 - 20/10/2022
197 - 08/11/2022
And not clients 1 and 34 due to having tickets past the 7th.
| client_id | created_at |
| 34 | 30/11/2022 10:23 |
| 34 | 01/12/2022 10:20 |
| 34 | 01/12/2022 15:17 |
| 34 | 01/12/2022 16:11 |
| 34 | 02/12/2022 11:32 |
| 34 | 02/12/2022 14:56 |
| 34 | 02/12/2022 16:38 |
| 34 | 05/12/2022 13:11 |
| 34 | 05/12/2022 13:11 |
| 34 | 06/12/2022 09:47 |
| 34 | 07/12/2022 09:14 |
| 34 | 08/12/2022 15:41 |
| 34 | 09/12/2022 08:59 |
| 34 | 09/12/2022 11:41 |
| 34 | 09/12/2022 15:23 |
| 34 | 09/12/2022 16:14 |
| 34 | 09/12/2022 16:17 |
| 34 | 09/12/2022 16:53 |
| 34 | 12/12/2022 13:44 |
| 34 | 13/12/2022 08:39 |
| 34 | 14/12/2022 08:44 |
| 34 | 14/12/2022 11:29 |
| 34 | 15/12/2022 10:27 |
| 34 | 16/12/2022 15:23 |
| 34 | 19/12/2022 12:11 |
| 34 | 19/12/2022 12:11 |
| 34 | 19/12/2022 16:50 |
| 34 | 21/12/2022 09:49 |
| 1 | 23/11/2022 12:53 |
| 1 | 23/11/2022 17:32 |
| 1 | 24/11/2022 05:08 |
| 1 | 24/11/2022 09:26 |
| 1 | 24/11/2022 09:26 |
| 1 | 24/11/2022 10:35 |
| 1 | 24/11/2022 15:26 |
| 1 | 25/11/2022 10:17 |
| 1 | 25/11/2022 10:19 |
| 1 | 25/11/2022 11:02 |
| 1 | 25/11/2022 12:02 |
| 1 | 28/11/2022 04:30 |
| 1 | 28/11/2022 04:31 |
| 1 | 28/11/2022 08:02 |
| 1 | 28/11/2022 10:02 |
| 1 | 28/11/2022 10:14 |
| 1 | 28/11/2022 11:14 |
| 1 | 28/11/2022 12:05 |
| 1 | 29/11/2022 09:44 |
| 1 | 29/11/2022 10:47 |
| 1 | 29/11/2022 14:10 |
| 1 | 29/11/2022 15:11 |
| 1 | 29/11/2022 15:20 |
| 1 | 29/11/2022 15:59 |
| 1 | 30/11/2022 10:02 |
| 1 | 30/11/2022 11:39 |
| 1 | 30/11/2022 11:54 |
| 1 | 30/11/2022 12:03 |
| 1 | 30/11/2022 14:11 |
| 1 | 30/11/2022 15:41 |
| 1 | 30/11/2022 15:47 |
| 1 | 01/12/2022 10:02 |
| 1 | 01/12/2022 10:48 |
| 1 | 01/12/2022 13:41 |
| 1 | 01/12/2022 14:29 |
| 1 | 02/12/2022 00:02 |
| 1 | 02/12/2022 00:07 |
| 1 | 02/12/2022 00:08 |
| 1 | 02/12/2022 12:02 |
| 1 | 03/12/2022 11:02 |
| 1 | 04/12/2022 22:09 |
| 1 | 05/12/2022 04:30 |
| 1 | 05/12/2022 08:02 |
| 1 | 05/12/2022 09:02 |
| 1 | 05/12/2022 10:02 |
| 1 | 05/12/2022 12:31 |
| 1 | 05/12/2022 12:49 |
| 1 | 05/12/2022 12:49 |
| 1 | 05/12/2022 13:00 |
| 1 | 05/12/2022 13:20 |
| 1 | 05/12/2022 14:35 |
| 1 | 06/12/2022 09:53 |
| 1 | 06/12/2022 12:02 |
| 1 | 06/12/2022 12:20 |
| 1 | 06/12/2022 13:23 |
| 1 | 07/12/2022 03:44 |
| 1 | 07/12/2022 10:05 |
| 1 | 13/12/2022 14:44 |
| 1 | 13/12/2022 15:15 |
| 1 | 13/12/2022 15:17 |
| 1 | 13/12/2022 16:55 |
| 1 | 13/12/2022 16:55 |
| 1 | 14/12/2022 08:58 |
| 1 | 19/12/2022 04:30 |
| 1 | 19/12/2022 08:02 |
| 1 | 19/12/2022 10:02 |
| 1 | 19/12/2022 12:35 |
| 1 | 20/12/2022 11:46 |
| 1 | 20/12/2022 13:35 |
| 1 | 20/12/2022 14:29 |
| 1 | 20/12/2022 15:46 |
| 1 | 20/12/2022 15:50 |
| 1 | 20/12/2022 16:38 |
| 1 | 20/12/2022 17:11 |
| 1 | 20/12/2022 17:23 |
| 1 | 20/12/2022 17:59 |
| 1 | 21/12/2022 13:35 |
| 1 | 21/12/2022 18:36 |
| 171 | 21/03/2022 16:58 |
| 171 | 23/03/2022 11:07 |
| 171 | 20/04/2022 10:28 |
| 171 | 16/05/2022 11:22 |
| 171 | 27/06/2022 09:40 |
| 171 | 07/07/2022 11:22 |
| 171 | 12/07/2022 13:28 |
| 171 | 14/07/2022 12:58 |
| 171 | 26/07/2022 17:34 |
| 171 | 19/08/2022 09:04 |
| 171 | 01/09/2022 15:16 |
| 171 | 06/09/2022 10:13 |
| 171 | 08/09/2022 11:25 |
| 171 | 27/09/2022 13:40 |
| 171 | 20/10/2022 14:32 |
| 197 | 20/09/2022 13:10 |
| 197 | 20/09/2022 13:34 |
| 197 | 20/09/2022 13:37 |
| 197 | 20/09/2022 13:43 |
| 197 | 20/09/2022 14:10 |
| 197 | 20/09/2022 14:19 |
| 197 | 26/09/2022 13:18 |
| 197 | 31/10/2022 12:02 |
| 197 | 08/11/2022 14:12 |
Solution SageThank you for sample data. Here is the code:
Customers with last ticket older than 2 weeks ago =
var customer_last_ticket =
MAXX(
DISTINCT('Sample'[client_id]);
MAX('Sample'[created_at])
)
return IF(DATEDIFF(customer_last_ticket;TODAY();DAY)>=14;customer_last_ticket)In the variable customer_last_ticket we are storing an information about last ticket per customer
Then if this ticket is older than 14 days ago we are displaying int 🙂
I hope that this is what you've wanted.
Just tried this again and still no luck sadly! I am glad to see it works for you with the test date bellow is a screenshot of what I have done with DB names covered:
I have client_id set as a whole number and created_at set as date time, does this reflect your structure within the test data?
The data is being pulled in from SQL on my end.- bolfri
I don't think so. Can you paste here an example data rows for customer 0?
Sorry, it's just another client number so the data would be roughly the same as the test data supplied.
Tried to attach a test file but cannot link any more.
Are you developing this as a measure or column?
Okay this has worked thank you so much!! I removed and re-added the data and it started working as expected much appreciated!!!