Showing latest date before specific date

[Anonymous]

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

[bolfri]

Try this one. 🙂

Lastticket older than 2 weeks ago =

MAXX(

    FILTER('Sample';DATEDIFF('Sample'[DateTime];TODAY();DAY)>=14);

    'Sample'[DateTime]

)

[Anonymous]

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 :').

[bolfri]

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.

[Anonymous]

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

[bolfri · Accepted 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.

[Anonymous]

Okay this has worked thank you so much!! I removed and re-added the data and it started working as expected much appreciated!!!

[Anonymous]

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?

[Anonymous]

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?

[bolfri]

This code that I've send you is a measure.

[johnt75]

Try 

Latest Ticket =
VAR LastTicket =
    MAX ( 'Table'[Ticket time] )
RETURN
    IF ( LastTicket <= ( TODAY () - 14 + TIME ( 23, 59, 59 ) ), LastTicket )

[Anonymous]

Just tried and unfortunatly it doesnt work.

[johnt75]

in what way? what values is it returning, or is it not returning any values ?

[Anonymous]

Appolagies that was super vauge.

Its showing no values:

[johnt75]

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.

[Anonymous]

Just checked and both are date time.