Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Showing latest date before specific date

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

 

Need Help
Tips and Tricks
Tutorial Requests
  • bolfri's avatar
    bolfri
    3 years ago

    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.

     

16 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    3 years ago

    Try 

    Latest Ticket =
VAR LastTicket =
    MAX ( 'Table'[Ticket time] )
RETURN
    IF ( LastTicket <= ( TODAY () - 14 + TIME ( 23, 59, 59 ) ), LastTicket )
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Just tried and unfortunatly it doesnt work.

      • johnt75's avatar
        johnt75
        Icon for Super User rankSuper User
        3 years ago

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

  • bolfri's avatar
    bolfri
    Icon for Solution Sage rankSolution Sage
    3 years ago
    Try this one. 🙂
     
    Lastticket older than 2 weeks ago =
    MAXX(
        FILTER('Sample';DATEDIFF('Sample'[DateTime];TODAY();DAY)>=14);
        'Sample'[DateTime]
    )

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
        bolfri
        Icon for Solution Sage rankSolution Sage
        3 years ago

        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.