Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all
I am trying only to show the client's latest ticket date if it's older than two weeks using DAX.
For example, today is the 21st and 2 weeks ago it was the 7th, I only want to show the clients where there latest ticket was on or before the 7th, SS for the structure:
The Latest ticket field is a date field set to the latest.
I have tried multiple methods, using Dax, as this has to be dynamic and based on today's date.
I have seen multiple methods online trying to set the latest ticket to show as 1 in another column using an IF statement but so far none seem to work am I missing something here?
Thanks in advance
Solved! Go to Solution.
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.
Proud to be a Super User!
Proud to be a Super User!
So this has worked but not exactly how I want it to work.
It pulls in each client's ticket from two weeks ago or from the last time two weeks ago.
But I would like to exclude clients where they have put recent tickets in.
The concept is to detect inactive clients on a support desk.
I have no idea why I am struggling with this so much when I know how to do it within Tableau :').
Your sample shows only one date per custmer, so I was assuming that in this column you have only one, last datetime to each customer. If you have multiple values in this column for each customer then you should put another sample data with such records and describe which customer you want to include or exlude. It will be easier.
Proud to be a Super User!
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 |
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.
Proud to be a Super User!
Okay this has worked thank you so much!! I removed and re-added the data and it started working as expected much appreciated!!!
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.
I don't think so. Can you paste here an example data rows for customer 0?
Proud to be a Super User!
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?
This code that I've send you is a measure.
Proud to be a Super User!
Try
Latest Ticket =
VAR LastTicket =
MAX ( 'Table'[Ticket time] )
RETURN
IF ( LastTicket <= ( TODAY () - 14 + TIME ( 23, 59, 59 ) ), LastTicket )
Just tried and unfortunatly it doesnt work.
in what way? what values is it returning, or is it not returning any values ?
Appolagies that was super vauge.
Its showing no values:
It works in my sample. Double check that the column which has the datetime in it is set as type datetime, both in Power Query and in Power BI desktop. If it was type text or something then that might explain it.
Just checked and both are date time.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |